pg_upgrade doesn't copy ICU rules on template0
Shihao reported at [1]/messages/by-id/CAGRkXqQ59JE=Rg21HjEOAuswaoYs89bSTM3QyoA2k2fT-t6Q+A@mail.gmail.com that pg_upgrade doesn't copy template0's ICU
rules. I think that was an oversight commit 30a53b792959, which added
ICU rules.
I'm starting this new thread for that issue, because it's independent
from the pg_upgrade --initdb feature being discussed in that thread.
Bohyun included a fix in the latest --initdb patch, below is my reply to
that part.
[1]: /messages/by-id/CAGRkXqQ59JE=Rg21HjEOAuswaoYs89bSTM3QyoA2k2fT-t6Q+A@mail.gmail.com
/messages/by-id/CAGRkXqQ59JE=Rg21HjEOAuswaoYs89bSTM3QyoA2k2fT-t6Q+A@mail.gmail.com
On 27/10/2026 16:49, Bohyun Lee wrote:
1. ICU rules are not copied.
Thanks for catching that. The patch is updated so the upgrade now copies
template0.daticurules.
I used a parameterized update so that multiline and long ICU rules are
preserved as well.
+ /* + * Copy ICU rules separately as a parameter: they can contain newlines or + * exceed the command and query buffers. Other databases' rules are + * restored by pg_dump/pg_restore. + */ + if (GET_MAJOR_VERSION(new_cluster.major_version) >= 1600) + { + const char *params[] = {locale->db_icu_rules}; + PGresult *res; + + res = PQexecParams(conn_new_template1, + "UPDATE pg_catalog.pg_database SET daticurules = $1 " + "WHERE datname = 'template0'", + 1, NULL, params, NULL, NULL, 0); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + pg_fatal("could not set ICU rules for new cluster's template0: %s", + PQerrorMessage(conn_new_template1)); + PQclear(res); + } +
PQescapeLiteral(), which is used to escape the other options, should
handle newlines just fine. And if we're worried about exceeding the
query buffer, I think we should teach executeQueryOrDie() to grow the
buffer. pg_log() might not deal nicely with arbitrarily long inputs
either, though.
Alternatively, switch to using PQexecParams() for all the queries in the
function. But I think I'd prefer executeQueryOrDie(), because on error,
it prints the full query with the params inlined, and I find that nicer
when debugging. If it was a large binary blob, that might be excessive,
but I think ICU rules should not be _that_ long.
P.S. Looking at set_locale_and_encoding(), why does it have separate
branches for different target cluster versions? We only support the
current version as the target. Looks like commit 206b44bb24b started
that trend, but it seems pointless.
- Heikki
Heikki Linnakangas <hlinnaka@iki.fi> writes:
Shihao reported at [1] that pg_upgrade doesn't copy template0's ICU
rules. I think that was an oversight commit 30a53b792959, which added
ICU rules.
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.
regards, tom lane
Hi,
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.
Thanks,
Shihao
Thanks, addressing this problem in a separate thread seems better.
Attached patch file contains a separate ICU-rules fix.
PQescapeLiteral(), which is used to escape the other options, should
handle newlines just fine. And if we're worried about exceeding the
query buffer, I think we should teach executeQueryOrDie() to grow the
buffer. pg_log() might not deal nicely with arbitrarily long inputs
either, though.
Agreed. The patch extends the existing UPDATE executed through
executeQueryOrDie() per Heikki's suggestion.
Both query and logging buffers now use StringInfo to grow as needed,
preserving long rules and full error diagnostics.
Why does it have separate branches for different target cluster versions?
Right, those branches are unnecessary because pg_upgrade only supports
its own major version as the target. I removed them, and kept only the
source version checks required for older catalog layouts.
Best regards,
Bohyun
On Thu, Oct 29, 2026 at 2:43 AM shihao zhong <zhong950419@gmail.com> wrote:
Show quoted text
Hi,
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.Thanks,
Shihao
Hi Bohyun,
Thanks for working on this.
I tested your v1 on master and pg_upgrade is looking good.
The patch needs to backport to 16.
Thanks,
Shihao
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
Hi Heikki,
Stepping back a bit though, what is the point of having any of these
-- encoding, datcollate, datctype, daticurules -- set on template0?
They are the defaults initdb picked, and createdb -T template0 uses
them. So I think pg_upgrade should carry them over as they are. I'll
leave it to you whether that design should change.
So if you specify "initdb --icu-rules", you cannot create databases with
any other locale provider.
This is because createdb() copies the template's ICU rules even for a
non-ICU
database.
The attached patch fixed it and it need to back to 17.
Thanks,
Shihao