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 <andrewbi...@gmail.com> Reported-by: Justin Pryzby <pry...@telsasoft.com> Discussion: https://postgr.es/m/cajnzarwkfru76_yi3dqvf_wl-mpvt54zmwaxfwjcexdhb76...@mail.gmail.com Discussion: https://postgr.es/m/Zh0aAH7tbZb-9HbC@pryzbyj2023 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/d9f686a72ee91f6773e5d2bc52994db8d7157a8e Modified Files -------------- src/backend/catalog/heap.c | 36 +++++++++++++++-- src/backend/catalog/pg_constraint.c | 43 +++++++++++++------- src/backend/commands/tablecmds.c | 65 +++++++++++++++++++++++++++---- src/bin/pg_dump/pg_dump.c | 26 +++++++++++-- src/include/catalog/pg_constraint.h | 2 +- src/test/regress/expected/constraints.out | 56 ++++++++++++++++++++++++++ src/test/regress/sql/constraints.sql | 22 +++++++++++ 7 files changed, 221 insertions(+), 29 deletions(-)