Re: pgsql: Fix restore of not-null constraints with inheritance - Mailing list pgsql-hackers

From Andrew Dunstan
Subject Re: pgsql: Fix restore of not-null constraints with inheritance
Date
Msg-id 3a642a59-e3c7-4050-95dd-af3b5412e5f7@dunslane.net
Whole thread Raw
In response to Re: pgsql: Fix restore of not-null constraints with inheritance  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Responses Re: pgsql: Fix restore of not-null constraints with inheritance
List pgsql-hackers


On 2024-04-18 Th 11:39, Alvaro Herrera wrote:
On 2024-Apr-18, Alvaro Herrera wrote:

On 2024-Apr-18, Alvaro Herrera wrote:

Lastly, make two changes to pg_dump: 1) do not try to drop a not-null
constraint that's marked as inherited; this allows a dump to restore
with no errors if a table with a PK inherits from another which also has
a PK; 2) avoid giving inherited constraints throwaway names, for the
rare cases where such a constraint survives after the restore.
Hmm, this last bit broke pg_upgrade on crake.  I'll revert this part,
meanwhile I'll be installing 9.2 to see if it can be fixed in a better way.
Eh, so:

1. running "make check" on pg_upgrade using an oldinstall pointing to
9.2 fails, because PostgreSQL::Test::Cluster doesn't support that
version -- it only goes back to 9.2.  How difficult was it to port it
back to all these old versions?


It's not that hard to make it go back to 9.2. Here's a version that's a couple of years old, but it supports versions all the way back to 7.2 :-)

If there's interest I'll work on supporting our official "old" versions (i.e. 9.2 and up)



2. running "make check" with an oldinstall pointing to 10 fails, because
the "invalid database" checks fail:

not ok 7 - invalid database causes failure status (got 0 vs expected 1)

#   Failed test 'invalid database causes failure status (got 0 vs expected 1)'
#   at t/002_pg_upgrade.pl line 407.
not ok 8 - invalid database causes failure stdout /(?^:invalid)/





3. Lastly, even if I put back the code that causes the failures on crake
and restore from 10 (and ignore those two problems), I cannot reproduce
the issues it reported.  Is crake running some funky code that's not
what "make check" on pg_upgrade does, perchance?


It's running the buildfarm cross version upgrade module. See <https://github.com/PGBuildFarm/client-code/blob/main/PGBuild/Modules/TestUpgradeXversion.pm>

It's choking on the change in constraint names between the dump of the pre-upgrade database and the dump of the post-upgrade database, e.g.


 CREATE TABLE public.rule_and_refint_t2 (
-    id2a integer CONSTRAINT pgdump_throwaway_notnull_0 NOT NULL NO INHERIT,
-    id2c integer CONSTRAINT pgdump_throwaway_notnull_1 NOT NULL NO INHERIT
+    id2a integer CONSTRAINT rule_and_refint_t2_id2a_not_null NOT NULL NO INHERIT,
+    id2c integer CONSTRAINT rule_and_refint_t2_id2c_not_null NOT NULL NO INHERIT );


look at the dumpdiff-REL9_2_STABLE file for the full list.

I assume that means pg_dump is generating names that pg_upgrade throws away? That seems ... unfortunate.

There is a perl module at src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm. This is used to adjust the dump files before we diff them. Maybe you can remedy the problem by adding some code in there.



cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Attachment

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: Transparent column encryption
Next
From: Peter Eisentraut
Date:
Subject: Re: [18] clarify the difference between pg_wchar, wchar_t, and Unicode code points