Horton

Re: Collation versioning

Thomas Munro <thomas.munro@enterprisedb.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@enterprisedb.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Douglas Doole <dougdoole@gmail.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Christoph Berg <myon@debian.org>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2018-09-18T23:34:27Z
Lists: pgsql-hackers 
On Wed, Sep 19, 2018 at 10:09 AM Stephen Frost <sfrost@snowman.net> wrote:
> * Douglas Doole (dougdoole@gmail.com) wrote:
> > > The CHECK constraint doesn't need to directly track that information-
> > > it should have a dependency on the column in the table and that's where
> > > the information would be recorded about the current collation version.
> >
> > Just to have fun throwing odd cases out, how would something like this be
> > recorded?
> >
> > Database default collation: en_US
> >
> > CREATE TABLE t (c1 TEXT, c2 TEXT, c3 TEXT,
> >      CHECK (c1 COLLATE "fr_FR" BETWEEN c2 COLLATE "fr_FR" AND c3 COLLATE
> > "fr_FR"));
> >
> > You could even be really warped and apply multiple collations on a single
> > column in a single constraint.
>
> Once it gets to an expression and not just a simple check, I'd think
> we'd record it in the expression..

Maybe I misunderstood, but I don't think it makes sense to have a
collation version "on the column in the table", because (1) that fails
to capture the fact that two CHECK constraints that were defined at
different times might have become dependent on two different versions
(you created one constraint before upgrading and the other after, now
the older one is invalidated and sounds the alarm but the second one
is fine), and (2) the table itself doesn't care about collation
versions since heap tables are unordered; there is no particular
operation on the table that would be the correct time to update the
collation version on a table/column.  What we're trying to track is
when objects that in some way depend on the version become
invalidated, so wherever we store it there's going to have to be a
version recorded per dependent object at its creation time, so that's
either new columns on every interested catalog table, or ...

-- 
Thomas Munro
http://www.enterprisedb.com

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.