Identifying when collations change

[Craig Ringer]

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.

>> 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.

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.

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

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services