Re: Should AT TIME ZONE be volatile?

On Sat, Nov 13, 2021 at 11:47 AM Ilya Anfimov <ilan(at)tzirechnoy(dot)com> wrote:
> 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).

Yeah, I had a system exactly like that working (that is, a way to run
arbitrary commands to capture version strings, that could be used to
hash your collation definition files, patches somewhere in the
archives), but then we thought it'd be better to use glibc versions,
and separately, to perhaps try to ask the glibc people to expose a
version. FreeBSD (at my request), Windows and ICU do expose versions
in a straightforward way, and we capture those.

> 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.

We built a system that at least detected the changes on a per-index
level, but failed to ship it in release 14. See revert commit, and
links back to previous commits and discussion:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ec48314708262d8ea6cdcb83f803fc83dd89e721

It didn't invalidate indexes, but complained about each individual
index on first access in each session, until you REINDEXed it. We
will try again :-)

In the archives you can find discussions of how to make a system that
tolerates multiple version existing at the same time as I think you're
getting at, like DB2 does. It's tricky, because it's code and data.
DB2 ships with N copies of ICU in it.