From d9f686a72ee91f6773e5d2bc52994db8d7157a8e Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 18 Apr 2024 15:35:15 +0200 Subject: Fix restore of not-null constraints with inheritance In tables with primary keys, pg_dump creates tables with primary keys by initially dumping them with throw-away not-null constraints (marked "no inherit" so that they don't create problems elsewhere), to later drop them once the primary key is restored. Because of a unrelated consideration, on tables with children we add not-null constraints to all columns of the primary key when it is created. If both a table and its child have primary keys, and pg_dump happens to emit the child table first (and its throw-away not-null) and later its parent table, the creation of the parent's PK will fail because the throw-away not-null constraint collides with the permanent not-null constraint that the PK wants to add, so the dump fails to restore. We can work around this problem by letting the primary key "take over" the child's not-null. This requires no changes to pg_dump, just two changes to ALTER TABLE: first, the ability to convert a no-inherit not-null constraint into a regular inheritable one (including recursing down to children, if there are any); second, the ability to "drop" a constraint that is defined both directly in the table and inherited from a parent (which simply means to mark it as no longer having a local definition). Secondarily, change ATPrepAddPrimaryKey() to acquire locks all the way down the inheritance hierarchy, in case we need to recurse when propagating constraints. These two changes allow pg_dump to reproduce more cases involving inheritance from versions 16 and older. 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. Reported-by: Andrew Bille Reported-by: Justin Pryzby Discussion: https://postgr.es/m/CAJnzarwkfRu76_yi3dqVF_WL-MpvT54zMwAxFwJceXdHB76bOA@mail.gmail.com Discussion: https://postgr.es/m/Zh0aAH7tbZb-9HbC@pryzbyj2023 --- src/test/regress/expected/constraints.out | 56 +++++++++++++++++++++++++++++++ src/test/regress/sql/constraints.sql | 22 ++++++++++++ 2 files changed, 78 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 51157181c64..d50dd1f61ab 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -321,6 +321,62 @@ ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; Inherits: atacc1 DROP TABLE ATACC1, ATACC2; +-- overridding a no-inherit constraint with an inheritable one +CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); +CREATE TABLE ATACC1 (a int); +CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); +NOTICE: merging column "a" with inherited definition +INSERT INTO ATACC3 VALUES (null); -- make sure we scan atacc3 +ALTER TABLE ATACC2 INHERIT ATACC1; +ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; +ERROR: column "a" of relation "atacc3" contains null values +DELETE FROM ATACC3; +ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; +\d+ ATACC[123] + Table "public.atacc1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | not null | | plain | | +Not-null constraints: + "ditto" NOT NULL "a" +Child tables: atacc2 + + Table "public.atacc2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | not null | | plain | | +Not-null constraints: + "a_is_not_null" NOT NULL "a" (local, inherited) +Inherits: atacc1 +Child tables: atacc3 + + Table "public.atacc3" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | not null | | plain | | +Not-null constraints: + "ditto" NOT NULL "a" (inherited) +Inherits: atacc2 + +ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null; +ALTER TABLE ATACC1 DROP CONSTRAINT ditto; +\d+ ATACC3 + Table "public.atacc3" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | | | plain | | +Inherits: atacc2 + +DROP TABLE ATACC1, ATACC2, ATACC3; +-- The same cannot be achieved this way +CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); +CREATE TABLE ATACC1 (a int, CONSTRAINT ditto NOT NULL a); +CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); +NOTICE: merging column "a" with inherited definition +ALTER TABLE ATACC2 INHERIT ATACC1; +ERROR: cannot add NOT NULL constraint to column "a" of relation "atacc2" with inheritance children +DETAIL: Existing constraint "a_is_not_null" is marked NO INHERIT. +DROP TABLE ATACC1, ATACC2, ATACC3; -- -- Check constraints on INSERT INTO -- diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 2efb63e9d8f..7a39b504a31 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -212,6 +212,28 @@ ALTER TABLE ATACC1 ADD NOT NULL a NO INHERIT; \d+ ATACC2 DROP TABLE ATACC1, ATACC2; +-- overridding a no-inherit constraint with an inheritable one +CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); +CREATE TABLE ATACC1 (a int); +CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); +INSERT INTO ATACC3 VALUES (null); -- make sure we scan atacc3 +ALTER TABLE ATACC2 INHERIT ATACC1; +ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; +DELETE FROM ATACC3; +ALTER TABLE ATACC1 ADD CONSTRAINT ditto NOT NULL a; +\d+ ATACC[123] +ALTER TABLE ATACC2 DROP CONSTRAINT a_is_not_null; +ALTER TABLE ATACC1 DROP CONSTRAINT ditto; +\d+ ATACC3 +DROP TABLE ATACC1, ATACC2, ATACC3; + +-- The same cannot be achieved this way +CREATE TABLE ATACC2 (a int, CONSTRAINT a_is_not_null NOT NULL a NO INHERIT); +CREATE TABLE ATACC1 (a int, CONSTRAINT ditto NOT NULL a); +CREATE TABLE ATACC3 (a int) INHERITS (ATACC2); +ALTER TABLE ATACC2 INHERIT ATACC1; +DROP TABLE ATACC1, ATACC2, ATACC3; + -- -- Check constraints on INSERT INTO -- -- cgit v1.2.3