Content-Type: multipart/alternative;
 boundary="------------0YjNwxwrYZflN27WkLnKapT3"
Message-ID: <47cdad4a-1bb3-4280-8dae-1980d2681b0e@gmail.com>
Date: Wed, 30 Jul 2025 08:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Test instability when pg_dump orders by OID
To: Noah Misch <noah@leadboat.com>, pgsql-hackers@postgresql.org
References: <20250707192654.9e.nmisch@google.com>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <20250707192654.9e.nmisch@google.com>
Archived-At: 
 <https://www.postgresql.org/message-id/47cdad4a-1bb3-4280-8dae-1980d2681b0e%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0YjNwxwrYZflN27WkLnKapT3
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello Noah,

07.07.2025 22:26, Noah Misch wrote:
> A 002_pg_upgrade.pl run got swapped order of tags "notnull_tbl1_upg nn" and
> "notnull_parent_upg nn" for the schema diff test that commit
> 172259afb563d35001410dc6daad78b250924038 added in v18:
>
> @@ -436873,14 +436873,14 @@
>   ALTER TABLE public.insert_tbl
>       ADD CONSTRAINT ne_insert_tbl_con CHECK (((x + z) = 1)) NOT ENFORCED;
>   --
> --- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
> +-- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
>   --
> -ALTER TABLE public.notnull_tbl1_upg
> +ALTER TABLE public.notnull_parent_upg
>       ADD CONSTRAINT nn NOT NULL a NOT VALID;
>   --
> --- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
> +-- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
>   --
> -ALTER TABLE public.notnull_parent_upg
> +ALTER TABLE public.notnull_tbl1_upg

It's rather funny that a few days before the fix is going to be pushed,
hippopotamus proved the need for it [1] (I saw no such failures on the
buildfarm before):
...
[17:09:56.372](2.577s) not ok 8 - dump outputs from original and restored regression databases match
[17:09:56.372](0.000s)
[17:09:56.372](0.000s) #   Failed test 'dump outputs from original and restored regression databases match'
[17:09:56.372](0.000s) #   at 
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/../../../src/test/perl/PostgreSQL/Test/Utils.pm 
line 800.
[17:09:56.373](0.000s) #          got: '1'
#     expected: '0'
=== diff of 
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/src_dump.sql_adjusted 
and 
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/dest_dump.sql_adjusted
=== stdout ===
--- 
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/src_dump.sql_adjusted 
2025-07-28 17:09:55.040029896 +0200
+++ 
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/dest_dump.sql_adjusted 
2025-07-28 17:09:56.208057237 +0200
@@ -436960,14 +436960,14 @@
  ALTER TABLE public.insert_tbl
      ADD CONSTRAINT ne_insert_tbl_con CHECK (((x + z) = 1)) NOT ENFORCED;
  --
--- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
+-- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
  --
-ALTER TABLE public.notnull_tbl1_upg
+ALTER TABLE public.notnull_parent_upg
      ADD CONSTRAINT nn NOT NULL a NOT VALID;
  --
--- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
+-- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: buildfarm
  --
-ALTER TABLE public.notnull_parent_upg
+ALTER TABLE public.notnull_tbl1_upg
      ADD CONSTRAINT nn NOT NULL a NOT VALID;
  --
  -- Name: notnul

Thank you for working on the fix!

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hippopotamus&dt=2025-07-28%2015%3A05%3A11

Best regards,
Alexander
--------------0YjNwxwrYZflN27WkLnKapT3
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Hello Noah,<br>
      <br>
      07.07.2025 22:26, Noah Misch wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:20250707192654.9e.nmisch@google.com">
      <pre wrap="" class="moz-quote-pre">A 002_pg_upgrade.pl run got swapped order of tags "notnull_tbl1_upg nn" and
"notnull_parent_upg nn" for the schema diff test that commit
172259afb563d35001410dc6daad78b250924038 added in v18:

@@ -436873,14 +436873,14 @@
 ALTER TABLE public.insert_tbl
     ADD CONSTRAINT ne_insert_tbl_con CHECK (((x + z) = 1)) NOT ENFORCED;
 --
--- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
+-- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
 --
-ALTER TABLE public.notnull_tbl1_upg
+ALTER TABLE public.notnull_parent_upg
     ADD CONSTRAINT nn NOT NULL a NOT VALID;
 --
--- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
+-- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public; Owner: nm
 --
-ALTER TABLE public.notnull_parent_upg
+ALTER TABLE public.notnull_tbl1_upg
</pre>
    </blockquote>
    <br>
    It's rather funny that a few days before the fix is going to be
    pushed,<br>
    hippopotamus proved the need for it [1] (I saw no such failures on
    the<br>
    buildfarm before):<br>
    ...<br>
    [17:09:56.372](2.577s) not ok 8 - dump outputs from original and
    restored regression databases match<br>
    [17:09:56.372](0.000s) <br>
    [17:09:56.372](0.000s) #   Failed test 'dump outputs from original
    and restored regression databases match'<br>
    [17:09:56.372](0.000s) #   at
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/../../../src/test/perl/PostgreSQL/Test/Utils.pm
    line 800.<br>
    [17:09:56.373](0.000s) #          got: '1'<br>
    #     expected: '0'<br>
    === diff of
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/src_dump.sql_adjusted
    and
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/dest_dump.sql_adjusted<br>
    === stdout ===<br>
    ---
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/src_dump.sql_adjusted   
    2025-07-28 17:09:55.040029896 +0200<br>
    +++
/home/buildfarm/hippopotamus/buildroot/REL_18_STABLE/pgsql.build/src/bin/pg_upgrade/tmp_check/tmp_test_v61D/dest_dump.sql_adjusted   
    2025-07-28 17:09:56.208057237 +0200<br>
    @@ -436960,14 +436960,14 @@<br>
     ALTER TABLE public.insert_tbl<br>
         ADD CONSTRAINT ne_insert_tbl_con CHECK (((x + z) = 1)) NOT
    ENFORCED;<br>
     --<br>
    --- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public;
    Owner: buildfarm<br>
    +-- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public;
    Owner: buildfarm<br>
     --<br>
    -ALTER TABLE public.notnull_tbl1_upg<br>
    +ALTER TABLE public.notnull_parent_upg<br>
         ADD CONSTRAINT nn NOT NULL a NOT VALID;<br>
     --<br>
    --- Name: notnull_parent_upg nn; Type: CONSTRAINT; Schema: public;
    Owner: buildfarm<br>
    +-- Name: notnull_tbl1_upg nn; Type: CONSTRAINT; Schema: public;
    Owner: buildfarm<br>
     --<br>
    -ALTER TABLE public.notnull_parent_upg<br>
    +ALTER TABLE public.notnull_tbl1_upg<br>
         ADD CONSTRAINT nn NOT NULL a NOT VALID;<br>
     --<br>
     -- Name: notnul<br>
    <br>
    Thank you for working on the fix!<br>
    <br>
    [1]
<a class="moz-txt-link-freetext" href="https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hippopotamus&amp;dt=2025-07-28%2015%3A05%3A11">https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hippopotamus&amp;dt=2025-07-28%2015%3A05%3A11</a><br>
    <br>
    Best regards,<br>
    Alexander<br>
  </body>
</html>

--------------0YjNwxwrYZflN27WkLnKapT3--