Re: Identifying when collations change

[OndÅej BÃlka <neleai at seznam dot cz>]

On Thu, Jul 09, 2015 at 08:42:16PM +0800, Craig Ringer wrote:
> On 9 July 2015 at 18:01, Mike Frysinger <vapier@gentoo.org> wrote:
> > On 09 Jul 2015 10:15, OndÅej BÃlka wrote:
> >> On Wed, Jul 08, 2015 at 02:10:40AM -0400, Mike Frysinger wrote:
> 
> >> > i don't know about a portable answer, but perhaps extending nl_langinfo would
> >> > be more on the painless side of things ?  adding a GNU-specific keyword that'd
> >> > return a hash of the collation data so you could easily check. </naive>
> 
> It'd want to be per-collation, so you don't do unnecessary work if a
> locale you aren't using changes.
> 
> Interesting idea. Even if it was implemented and committed now,
> though, it'd be *years* before anyone could rely on it.
> 
> I was hoping for something along these lines, though. An interface
> that offers access to a version counter for a locale, which changes
> only when locale is modified.
>
That doesn't exist now. Also it would need manual bumping in bugfix
case. Without that you don't need any addition, as you could produce
these hashes by taking md5 of appropriate files in

/usr/share/i18n/locales/

In bugfix case you would probably need to reindex everything.
 
> >> A simple solution would be checking libc.so timestamp and reindexing when it
> >> changes, would reindexing once per year if user regularly updates
> >> matter?
> 
> That'd be hard to get right and would be likely to force lots of
> unnecessary work. REINDEXing multiple composite indexes on a 200GB
> table is no fun at all. Especially since at the moment PostgreSQL's
> ability to handle reindexing while concurrent work proceeds on the
> table is rather limited, but of course that's PostgreSQL's problem.
>
Still as upgrading libc causes restarting of PostgreSQL it shouldn't
matter that much.
 
> It would also not be usable for detecting collation mismatches across
> different hosts, where a replica of the database is getting different
> strcoll results than the primary.
> 
Timestamp was first idea. Better would be version by following:

#include <stdio.h>
#include <gnu/libc-version.h>
int main (void) { puts (gnu_get_libc_version ()); return 0; }

> Is there any sane, generic way we might generate a collation
> "fingerprint" application-side? Or anything within glibc that could
> help?
> 
Not now.