Re: Collation versioning

On Mon, Sep 17, 2018 at 9:02 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Thomas Munro (thomas(dot)munro(at)enterprisedb(dot)com) wrote:
> > On Mon, Sep 17, 2018 at 6:13 AM Douglas Doole <dougdoole(at)gmail(dot)com> wrote:
> > > On Sun, Sep 16, 2018 at 1:20 AM Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> > >> 3. Fix the tracking of when reindexes need to be rebuilt, so that you
> > >> can't get it wrong (as you're alluding to above).
> > >
> > > I've mentioned this in the past, but didn't seem to get any traction, so I'll try it again ;-)
> >
> > Probably because we agree with you, but don't have all the answers :-)
>
> Agreed.
>
> > > The focus on indexes when a collation changes is, in my opinion, the least of the problems. You definitely have to worry about indexes, but they can be easily rebuilt. What about other places where collation is hardened into the system, such as constraints?
> >
> > We have to start somewhere and indexes are the first thing that people
> > notice, and are much likely to actually be a problem (personally I've
> > encountered many cases of index corruption due to collation changes in
> > the wild, but never a constraint corruption, though I fully understand
> > the theoretical concern). Several of us have observed specifically
> > that the same problems apply to CHECK constraints and PARTITION
> > boundaries, and there may be other things like that. You could
> > imagine tracking collation dependencies on those, requiring a RECHECK
> > or REPARTITION operation to update them after a depended-on collation
> > version changes.
> >
> > Perhaps that suggests that there should be a more general way to store
> > collation dependencies -- something more like pg_depend, rather than
> > bolting something like indcollversion onto indexes and every other
> > kind of catalog that might need it. I don't know.
>
> Agreed. If we start thinking about pg_depend then maybe we realize
> that this all comes back to pg_attribute as the holder of the
> column-level information and maybe what we should be thinking about is a
> way to encode version information into the typmod for text-based
> types...

So to be more concrete: pg_depend could have a new column
"refobjversion". Whenever indexes are created or rebuilt, we'd
capture the current version string in the pg_depend rows that link
index attributes and collations. Then we'd compare those against the
current value when we first open an index and complain if they don't
match. (In this model there would be no "collversion" column in the
pg_collation catalog.)

That'd leave a place for other kinds of database objects (CHECKs,
PARTITIONS, ...) to store their version dependency, if someone later
wants to add support for that.

I'm not sure if my idea about updating the default collation row in
newly created databases has legs though. Any thoughts on that?

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