Re: Test to dump and restore objects left behind by regression

On Thu, Mar 13, 2025 at 2:12 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> Hello
>
> On 2025-Mar-13, Ashutosh Bapat wrote:
>
> > 1. can you please run the test again and share the dump outputs. They
> > will be located in a temporary directory with names
> > src_dump.sql_adjusted and dest_dump.<format>.sql_adjusted.
>
> Ah, I see the problem :-) The first initdb does this:
>
> # Running: initdb -D /home/alvherre/Code/pgsql-build/master/src/bin/pg_upgrade/tmp_check/t_002_pg_upgrade_old_node_data/pgdata -A trust -N --wal-segsize 1 --allow-group-access --encoding UTF-8 --lc-collate C --lc-ctype C --locale-provider builtin --builtin-locale C.UTF-8 -k
> The files belonging to this database system will be owned by user "alvherre".
> This user must also own the server process.
>
> The database cluster will be initialized with this locale configuration:
> locale provider: builtin
> default collation: C.UTF-8
> LC_COLLATE: C
> LC_CTYPE: C
> LC_MESSAGES: C
> LC_MONETARY: es_CL.UTF-8
> LC_NUMERIC: es_CL.UTF-8
> LC_TIME: es_CL.UTF-8
> The default text search configuration will be set to "english".
>
> Data page checksums are enabled.
>
> which for some reason used my environment setting for LC_MONETARY.
>

Thanks. This is super helpful. I am able to reproduce the problem
$ unset LC_MONETARY
$ export PG_TEST_EXTRA=regress_dump_test
$ meson test --suite setup && meson test pg_upgrade/002_pg_upgrade
... snip ...
1/1 postgresql:pg_upgrade / pg_upgrade/002_pg_upgrade OK
72.38s 44 subtests passed

Ok: 1
Expected Fail: 0
Fail: 0
Unexpected Pass: 0
Skipped: 0
Timeout: 0

Full log written to
/home/ashutosh/work/units/pg_dump_test/build/dev/meson-logs/testlog.txt
$ export LC_MONETARY="es_CL.UTF-8"
$ meson test --suite setup && meson test pg_upgrade/002_pg_upgrade
... snip ...
1/1 postgresql:pg_upgrade / pg_upgrade/002_pg_upgrade ERROR
69.18s exit status 4
>>> with_icu=no LD_LIBRARY_PATH=/home/ashutosh/work/units/pg_dump_test/build/dev/tmp_install//home/ashutosh/work/units/pg_dump_test/build/dev/lib/x86_64-linux-gnu REGRESS_SHLIB=/home/ashutosh/work/units/pg_dump_test/build/dev/src/test/regress/regress.so PATH=/home/ashutosh/work/units/pg_dump_test/build/dev/tmp_install//home/ashutosh/work/units/pg_dump_test/build/dev/bin:/home/ashutosh/work/units/pg_dump_test/build/dev/src/bin/pg_upgrade:/home/ashutosh/work/units/pg_dump_test/build/dev/src/bin/pg_upgrade/test:/home/ashutosh/work/units/pg_dump_test/build/dev/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin:/snap/bin MALLOC_PERTURB_=30 share_contrib_dir=/home/ashutosh/work/units/pg_dump_test/build/dev/tmp_install//home/ashutosh/work/units/pg_dump_test/build/dev/share/postgresql/contrib PG_REGRESS=/home/ashutosh/work/units/pg_dump_test/build/dev/src/test/regress/pg_regress top_builddir=/home/ashutosh/work/units/pg_dump_test/build/dev INITDB_TEMPLATE=/home/ashutosh/work/units/pg_dump_test/build/dev/tmp_install/initdb-template /usr/bin/python3 /home/ashutosh/work/units/pg_dump_test/build/dev/../../coderoot/pg/src/tools/testwrap --basedir /home/ashutosh/work/units/pg_dump_test/build/dev --srcdir /home/ashutosh/work/units/pg_dump_test/coderoot/pg/src/bin/pg_upgrade --pg-test-extra '' --testgroup pg_upgrade --testname 002_pg_upgrade -- /usr/bin/perl -I /home/ashutosh/work/units/pg_dump_test/coderoot/pg/src/test/perl -I /home/ashutosh/work/units/pg_dump_test/coderoot/pg/src/bin/pg_upgrade /home/ashutosh/work/units/pg_dump_test/coderoot/pg/src/bin/pg_upgrade/t/002_pg_upgrade.pl

Ok: 0
Expected Fail: 0
Fail: 1
Unexpected Pass: 0
Skipped: 0
Timeout: 0

I see what's happening. If I set LC_MONETARY environment explicitly,
that's taken by initdb
$ export LC_MONETARY="es_CL.UTF-8";rm -rf $DataDir; $BinDir/initdb -D
$DataDir -A trust -N --wal-segsize 1 --allow-group-access --encoding
UTF-8 --lc-collate C --lc-ctype C --locale-provider builtin
--builtin-locale C.UTF-8 -k
The files belonging to this database system will be owned by user "ashutosh".
This user must also own the server process.

The database cluster will be initialized with this locale configuration:
locale provider: builtin
default collation: C.UTF-8
LC_COLLATE: C
LC_CTYPE: C
LC_MESSAGES: en_US.UTF-8
LC_MONETARY: es_CL.UTF-8
LC_NUMERIC: en_US.UTF-8
LC_TIME: en_US.UTF-8
The default text search configuration will be set to "english".

If I don't set it explicitly, it's taken from default settings
$ unset LC_MONETARY;rm -rf $DataDir; $BinDir/initdb -D $DataDir -A
trust -N --wal-segsize 1 --allow-group-access --encoding UTF-8
--lc-collate C --lc-ctype C --locale-provider builtin --builtin-locale
C.UTF-8 -k
The files belonging to this database system will be owned by user "ashutosh".
This user must also own the server process.

The database cluster will be initialized with this locale configuration:
locale provider: builtin
default collation: C.UTF-8
LC_COLLATE: C
LC_CTYPE: C
LC_MESSAGES: en_US.UTF-8
LC_MONETARY: en_US.UTF-8
LC_NUMERIC: en_US.UTF-8
LC_TIME: en_US.UTF-8
The default text search configuration will be set to "english".

In your case probably your default setting is es_CL.UTF-8 or have set
LC_MONETARY explicitly in your environment.

I think the fix is to explicitly pass --lc-monetary to the old cluster
and the restored cluster. 003 patch in the attached patch set does
that. Please check if it fixes the issue for you.

Additionally we should check that it gets copied to the new cluster as
well. But I haven't figured out how to get those settings yet. This
treatment is similar to how --lc-collate and --lc-ctype are treated. I
am wondering whether we should explicitly pass --lc-messages,
--lc-time and --lc-numeric as well.

2d819a08a1cbc11364e36f816b02e33e8dcc030b introduced buildin locale
provider and added overrides to LC_COLLATE and LC_TYPE. But it did not
override other LC_, which I think it should have. In pure upgrade
test, the upgraded node inherits the locale settings of the original
cluster, so this wasn't apparent. But with pg_dump testing, the
original and restored databases are independent. Hence I think we have
to override all LC_* settings by explicitly mentioning --lc-* options
to initdb. Please let me know what you think about this?

--
Best Wishes,
Ashutosh Bapat