Horton

Re: Collation versioning

Laurenz Albe <laurenz.albe@cybertec.at>

View thread View on postgr.es
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Thomas Munro <thomas.munro@gmail.com>, Michael Paquier <michael@paquier.xyz>
Cc: Julien Rouhaud <rjuju123@gmail.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Douglas Doole <dougdoole@gmail.com>, Christoph Berg <myon@debian.org>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-11-08T04:40:31Z
Lists: pgsql-hackers 
On Fri, 2019-11-08 at 15:04 +1300, Thomas Munro wrote:
> So we have three proposals:
> 
> 1.  Assume that pre-13 indexes that depend on collations are
> potentially corrupted and complain until they are reindexed.  This
> could be done by having pg_upgrade run ALTER INDEX ... DEPENDS ON
> COLLATION "fr_FR" VERSION '' (empty string, or some other default
> value that we don't think is going to coincide with a real version).
> 2.  Assume that pre-13 indexes are not corrupted.  In the target 13
> database, the index will be created in the catalogs with the
> provider's current version.
> 3.  We don't know if pre-13 indexes are corrupted or not, and we'll
> record that with a special value just as in proposal #1, except that
> we could show a different hint for that special version value.  It
> would tell you can you can either REINDEX, or run ALTER INDEX ...
> DEPENDS ON COLLATION "fr_FR" VERSION '34.0' if you believe the index
> to have been created with the current collation version on an older
> release of PostgreSQL that didn't track versions.

I think #1 is bad because it would frighten all users, even those who
didn't upgrade their libc at all, only PostgreSQL.  I don't think that
shouting "wolf" for no real reason will improve trust in PostgreSQL.

#2 is bad because it may hide pre-existing index corruption.

#3 is the best proposal, but there is still the need to run
ALTER INDEX on all affected indexes to keep PostgreSQL from nagging.
Perhaps the situation could be improved with a pg_upgrade option
--i-know-my-indexes-are-fine that causes a result like #2.
Together with a bold note in the release notes, this may relieve
the pain.

Yours,
Laurenz Albe

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.