Re: pg_upgrade doesn't copy ICU rules on template0

On 29/10/2026 03:43, shihao zhong wrote:
>> Uh ... say what? template0 is supposed to be immutable after initdb.
>> I think the actual problem here is with whatever patch introduced
>> the possibility of this value changing.
>
> Users can't change template0 after initdb. The value changes inside
> pg_upgrade. Since 16, set_locale_and_encoding() copies the old
> template0's locale fields into the new template0 (9637badd9f9). That
> way the new cluster doesn't need the same initdb options. It misses
> daticurules.

Right. We surely should treat daticurules the same as encoding and
collation here.

Stepping back a bit though, what is the point of having any of these --
encoding, datcollate, datctype, daticurules -- set on template0? The
only thing you can do with template0 is to use it as template with
CREATE DATABASE, and you can override all of those values at that point.
So they just act as a default, when you use CREATE DATABASE. But a very
inflexible default at that, since you cannot change them after initdb.

I also noticed this:

First initialize cluster with --icu-rules:

initdb --icu-rules='rulez' --locale-provider='icu' --icu-locale=fi

Now try to create a non-ICU database:

postgres=# create database foodb WITH template template0
LOCALE_PROVIDER='builtin';
ERROR: ICU rules cannot be specified unless locale provider is ICU

Ok, fair enough. But since the ICU rules are coming from the template,
how do I specify "no ICU rules"? This doesn't work:

postgres=# create database foodb WITH template template0
LOCALE_PROVIDER='builtin' ICU_RULES='';
ERROR: ICU rules cannot be specified unless locale provider is ICU

So if you specify "initdb --icu-rules", you cannot create databases with
any other locale provider.

- Heikki