Re: Test to dump and restore objects left behind by regression - Mailing list pgsql-hackers

On Wed, Mar 12, 2025 at 5:35 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> Hello
>
> When running these tests, I encounter this strange diff in the dumps,
> which seems to be that the locale for type money does not match.  I
> imagine the problem is that the locale is not set correctly when
> initdb'ing one of them?  Grepping the regress_log for initdb, I see
> this:
>
> $ grep -B1 'Running: initdb' tmp_check/log/regress_log_002_pg_upgrade
> [13:00:57.580](0.003s) # initializing database system by running initdb
> # 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-segsize1 --allow-group-access --encoding UTF-8 --lc-collate C --lc-ctype C --locale-provider builtin
--builtin-localeC.UTF-8 -k 
> --
> [13:01:12.879](0.044s) # initializing database system by running initdb
> # Running: initdb -D
/home/alvherre/Code/pgsql-build/master/src/bin/pg_upgrade/tmp_check/t_002_pg_upgrade_dst_node_data/pgdata-A trust -N
--wal-segsize1 --allow-group-access --encoding UTF-8 --lc-collate C --lc-ctype C --locale-provider builtin
--builtin-localeC.UTF-8 -k 
> --
> [13:01:28.000](0.033s) # initializing database system by running initdb
> # Running: initdb -D
/home/alvherre/Code/pgsql-build/master/src/bin/pg_upgrade/tmp_check/t_002_pg_upgrade_new_node_data/pgdata-A trust -N
--wal-segsize1 --allow-group-access --encoding SQL_ASCII --locale-provider libc 
>

The original node and the node where dump is restored have the same
initdb commands. It's the upgraded node which has different initdb
command. But that's how the test is written originally.

>
>
> [12:50:31.838](0.102s) not ok 15 - dump outputs from original and restored regression database (using plain format)
match
> [12:50:31.839](0.000s)
> [12:50:31.839](0.000s) #   Failed test 'dump outputs from original and restored regression database (using plain
format)match' 
> #   at /pgsql/source/master/src/test/perl/PostgreSQL/Test/Utils.pm line 797.
> [12:50:31.839](0.000s) #          got: '1'
> #     expected: '0'
> === diff of /home/alvherre/Code/pgsql-build/master/src/bin/pg_upgrade/tmp_check/tmp_test_vVew/src_dump.sql_adjusted
and/home/alvherre/Code/pgsql-build/master/src/bin/pg_upgrade/tmp_check/tmp_test_vVew/dest_dump.plain.sql_adjusted 
> === stdout ===
> --- /home/alvherre/Code/pgsql-build/master/src/bin/pg_upgrade/tmp_check/tmp_test_vVew/src_dump.sql_adjusted
2025-03-1212:50:27.674918597 +0100 
> +++ /home/alvherre/Code/pgsql-build/master/src/bin/pg_upgrade/tmp_check/tmp_test_vVew/dest_dump.plain.sql_adjusted
 2025-03-12 12:50:31.778840338 +0100 
> @@ -208972,7 +208972,7 @@
>  -- Data for Name: money_data; Type: TABLE DATA; Schema: public; Owner: alvherre
>  --
>  COPY public.money_data (m) FROM stdin;
> -$123.46
> +$ 12.346,00
>  \.
>  --
>  -- Data for Name: mvtest_t; Type: TABLE DATA; Schema: public; Owner: alvherre
> @@ -376231,7 +376231,7 @@
>  -- Data for Name: tab_core_types; Type: TABLE DATA; Schema: public; Owner: alvherre
>  --
>  COPY public.tab_core_types (point, line, lseg, box, openedpath, closedpath, polygon, circle, date, "time",
"timestamp",timetz, timestamptz, "interval", "json", jsonb, jsonpath, inet, cidr, macaddr8, macaddr, int2, int4, int8,
float4,float8, pi, "char", bpchar, "varchar", name, text, bool, bytea, "bit", varbit, money, refcursor, int2vector,
oidvector,aclitem, tsvector, tsquery, uuid, xid8, regclass, type, regrole, oid, tid, xid, cid, txid_snapshot,
pg_snapshot,pg_lsn, cardinal_number, character_data, sql_identifier, time_stamp, yes_or_no, int4range, int4multirange,
int8range,int8multirange, numrange, nummultirange, daterange, datemultirange, tsrange, tsmultirange, tstzrange,
tstzmultirange)FROM stdin; 
> -(11,12)        {1,-1,0}        [(11,11),(12,12)]       (13,13),(11,11) ((11,12),(13,13),(14,14))
[(11,12),(13,13),(14,14)]      ((11,12),(13,13),(14,14))       <(1,1),1>       2025-03-12      04:50:14.125899
2025-03-1204:50:14.125899      04:50:14.125899-07      2025-03-12 12:50:14.125899+01   00:00:12
{"reason":"because"}   {"when": "now"} $."a"[*]?(@ > 2)        127.0.0.1       127.0.0.0/8     00:01:03:ff:fe:86:1c:ba
00:01:03:86:1c:ba      2       4       8       4       8       3.14159265358979        f       c       abc     name
txt    t       \\xdeadbeef     1       10001   $12.34  abc     1 2     1 2     alvherre=UC/alvherre    'a' 'and' 'ate'
'cat''fat' 'mat' 'on' 'rat' 'sat'      'fat' & 'rat'   a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11    11      pg_class
regtypepg_monitor      1259    (1,1)   2       3       10:20:10,14,15  10:20:10,14,15  16/B374D848     1       l
n      2025-03-12 12:50:14.13+01       YES     empty   {}      empty   {}      (3,4)   {(3,4)} [2020-01-03,2021-02-03)
{[2020-01-03,2021-02-03)}      ("2020-01-02 03:04:05","2021-02-03 06:07:08")   {("2020-01-02 03:04:05","2021-02-03
06:07:08")}("2020-01-02 12:04:05+01","2021-02-03 15:07:08+01")     {("2020-01-02 12:04:05+01","2021-02-03
15:07:08+01")}
> +(11,12)        {1,-1,0}        [(11,11),(12,12)]       (13,13),(11,11) ((11,12),(13,13),(14,14))
[(11,12),(13,13),(14,14)]      ((11,12),(13,13),(14,14))       <(1,1),1>       2025-03-12      04:50:14.125899
2025-03-1204:50:14.125899      04:50:14.125899-07      2025-03-12 12:50:14.125899+01   00:00:12
{"reason":"because"}   {"when": "now"} $."a"[*]?(@ > 2)        127.0.0.1       127.0.0.0/8     00:01:03:ff:fe:86:1c:ba
00:01:03:86:1c:ba      2       4       8       4       8       3.14159265358979        f       c       abc     name
txt    t       \\xdeadbeef     1       10001   $ 1.234,00      abc     1 2     1 2     alvherre=UC/alvherre    'a'
'and''ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'      'fat' & 'rat'   a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11    11
pg_class       regtype pg_monitor      1259    (1,1)   2       3       10:20:10,14,15  10:20:10,14,15  16/B374D848
1      l       n       2025-03-12 12:50:14.13+01       YES     empty   {}      empty   {}      (3,4)   {(3,4)}
[2020-01-03,2021-02-03){[2020-01-03,2021-02-03)}       ("2020-01-02 03:04:05","2021-02-03 06:07:08")   {("2020-01-02
03:04:05","2021-02-0306:07:08")} ("2020-01-02 12:04:05+01","2021-02-03 15:07:08+01")     {("2020-01-02
12:04:05+01","2021-02-0315:07:08+01")} 
>  \.
>  --
>  -- Data for Name: tableam_parted_a_heap2; Type: TABLE DATA; Schema: public; Owner: alvherre=== stderr ===
> === EOF ===
>

However these differences are coming from original and restored
database which are using the same initdb options.

Does the test pass for you if you don't apply my patches?

Over at [1], I had seen a locale related failure without applying my patches.

[1] https://www.postgresql.org/message-id/CAExHW5s+XNiP8aPGw9=hvbjdoOG5A-QCJnDdRcKsY1rDdZe4Jw@mail.gmail.com

--
Best Wishes,
Ashutosh Bapat