Horton

Re: Collation versioning

Stephen Frost <sfrost@snowman.net>

View thread View on postgr.es
From: Stephen Frost <sfrost@snowman.net>
To: Christoph Berg <myon@debian.org>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Thomas Munro <thomas.munro@enterprisedb.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2018-09-13T11:45:49Z
Lists: pgsql-hackers 
Greetings,

* Christoph Berg (myon@debian.org) wrote:
> Re: Peter Eisentraut 2018-09-13 <4f60612c-a7b5-092d-1532-21ff7a106bd5@2ndquadrant.com>
> > Moreover, the fix for a collation version mismatch is, in the simplest
> > case, to go around and REINDEX everything.  Making the collation or
> > collation version global doesn't fix that.  It would actually make it
> > harder because you couldn't run ALTER COLLATION REFRESH VERSION until
> > after you have rebuilt all affected objects *in all databases*.
> 
> Btw, I think a "reindexdb --all --collation" (and the SQL per-database
> equivalent) that only rebuilds indexes that are affected by collations
> would be immensely useful to have.

As I was discussing w/ Peter G during PostgresOpen, we'd have to wait
until that reindexdb is complete before actually using anything in the
system and that's pretty painful.  While it sounds like it'd be a good
bit of work, it seems like we really need to have a way to support
multiple collation versions concurrently and to do that we'll need to
have the library underneath actually providing that to us.  Once we have
that, we can build new indexes concurrently and swap to them (or,
ideally, just issue REINDEX CONCURRENTLY once we support that..).

Until then, it seems like we really need to have a way to realize that a
given upgrade is going to require a big reindex, before actually doing
the reindex and suddenly discovering that we can't use a bunch of
indexes because they're out of date and extending the downtime for the
upgrade to be however long it takes to rebuild those indexes...

Thanks!

Stephen

Commits

  1. Doc: Document known problem with Windows collation versions.

  2. Add collation versions for FreeBSD.

  3. Tolerate version lookup failure for old style Windows locale names.

  4. Track collation versions for indexes.

  5. Add pg_depend.refobjversion.

  6. Remove pg_collation.collversion.

  7. Fix the MSVC build for versions 2015 and later.

  8. Add collation versions for Windows.

  9. Implement type regcollation

  10. Use libc version as a collation version on glibc systems.

  11. Make type "name" collation-aware.