Horton

Re: Collation versioning

David Rowley <dgrowleyml@gmail.com>

View thread View on postgr.es
From: David Rowley <dgrowleyml@gmail.com>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Julien Rouhaud <rjuju123@gmail.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Michael Paquier <michael@paquier.xyz>, Robert Haas <robertmhaas@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, Douglas Doole <dougdoole@gmail.com>, Christoph Berg <myon@debian.org>, Pg Hackers <pgsql-hackers@postgresql.org>, Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
Date: 2020-11-04T01:56:30Z
Lists: pgsql-hackers 
On Wed, 4 Nov 2020 at 14:21, Thomas Munro <thomas.munro@gmail.com> wrote:
>
> On Wed, Nov 4, 2020 at 10:56 AM Thomas Munro <thomas.munro@gmail.com> wrote:
> > On Wed, Nov 4, 2020 at 10:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Thomas Munro <thomas.munro@gmail.com> writes:
> > > > We want the same algorithm that Windows uses internally to resolve the
> > > > old style name to a collation; in other words we probably want
> > > > something more like the code path that they took away in VS2015 :-(.
> > >
> > > Yeah.  In the short run, though, it'd be nice to un-break the buildfarm.
> > > Maybe we could push David's code or something similar, and then
> > > contemplate better ways at leisure?
> >
> > Ok, yeah, I'll do that in the next few hours.
>
> I can't bring myself to commit that, it's not really in the spirit of
> this data integrity feature, and it's not our business to second guess
> the relationship between different locale naming schemes through fuzzy
> logic.  Instead, I propose to just neuter the feature if Windows
> decides it can't understand a locale names that it gave us.  It should
> still work fine with something like initdb --lc-collate=en-US.  Here's
> an untested patch.  Thoughts?

I gave this a quick test.

initdb works fine. I ran vcregress upgradecheck and it passes.

With my default locale of English.New Zealand.1252 I get zero rows from:

select * from pg_depend where coalesce(refobjversion,'') <> '';

if I initdb with --lc-collate=en-NZ, it works and I see:

postgres=# select * from pg_depend where coalesce(refobjversion,'') <> '';
 classid | objid | objsubid | refclassid | refobjid | refobjsubid |
deptype |  refobjversion
---------+-------+----------+------------+----------+-------------+---------+-----------------
    2606 | 12512 |        0 |       3456 |      100 |           0 | n
     | 1538.14,1538.14
(1 row)

David

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.