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 <[email protected]>
Reported-by: Justin Pryzby <[email protected]>
Discussion: https://postgr.es/m/CAJnzarwkfRu76_yi3dqVF_WL-MpvT54zMwAxFwJceXdHB76bOA@mail.gmail.com
Discussion: https://postgr.es/m/Zh0aAH7tbZb-9HbC@pryzbyj2023

1 files changed, 22 insertions, 0 deletions

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
 --