Re: Database-level collation version tracking

On Wed, Feb 09, 2022 at 05:12:41PM +0100, Peter Eisentraut wrote:
> On 08.02.22 13:55, Julien Rouhaud wrote:
> > Apart from that I still think that we should check the collation version of the
> > source database when creating a new database. It won't cost much but will give
> > the DBA a chance to recreate the indexes before risking invalid index usage.
>
> A question on this: In essence, this would be putting code into createdb()
> similar to the code in postinit.c:CheckMyDatabase(). But what should we
> make it do and say exactly?
>
> After thinking about this for a bit, I suggest: If the actual collation
> version of the newly created database does not match the recorded collation
> version of the template database, we should error out and make the user fix
> the template database (by reindexing there etc.).

I'm not sure what you really mean by "actual collation version of the newly
created database". Is it really a check after having done all the work or just
checking a discrepancy when computing the to-be-created database version from
the source database, ie. something like

if (dbcollversion == NULL)
+ {
dbcollversion = src_collversion;
+ if src_collversion != get_collation_actual_version(the source db)
+ // raise error or warning

> The alternative is to warn, as it does now in postinit.c. But then the
> phrasing of the message becomes complicated: Should we make the user fix the
> new database or the template database or both? And if they don't fix the
> template database, they will have the same problem again. So making it a
> hard failure seems better to me.

Agreed, I'm in favor of a hard error. Maybe a message like:

errmsg(cannot create database %s)
errdetail(the template database %s was created using collation version %s, but
the operating system provides version %s)

Also, that check shouldn't be done when using the COLLATION_VERSION option of
create database, since it's there for pg_upgrade usage?