Re: Should AT TIME ZONE be volatile?

On Thu, Nov 11, 2021 at 09:52:52AM -0500, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I'm not really convinced that ICU is better, either. I think it's more
> > that it isn't used as much.
>
> Well, at least ICU has a notion of attaching versions to collations.
> How mindful they are of bumping the version number when necessary
> remains to be seen. But the POSIX locale APIs don't even offer the
> opportunity to get it right.
>
> > I don't have any constructive proposal for what to do about any of
> > this. It sure is frustrating, though.
>
> Yup. If we had reliable ways to detect changes in this sort of
> environment-supplied data, maybe we could do something about it
> (a la the work that's been happening on attaching collation versions
> to indexes). But personally I can't summon the motivation to work

Theoretically there are versions attached to collations already:
the collation in index is an oid referencing the pg_collation.
And the pg_collation already has versions.

Currently for glibc the version looks like glibc version at
initdb, and that doesn't seem very reliable, but that could be a
different task (to find LC_COLLATE file and put hash of the
usuable data into version string, for example).

Currently, it is questionable how to work with the different
versions of collations -- but that could be solved e.g. via ap-
propriate naming. Perhaps "collation(at)ver" ? But if the version
would contain a hash, a full version could be a bit dubious.
And some database maintainance task could check that all the old
collations are available, rename them as needed, and create a set
the new ones.
Automatically invalidating all the indexes, unfortunately.

> on that, when ICU is the *only* such infrastructure that offers
> readily program-readable versioning.
>
> regards, tom lane
>