From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix restore of not-null constraints with inheritance
Date: Thu, 18 Apr 2024 13:37:43 +0000
Message-ID: <E1rxRxO-002hM4-Lk@gemulon.postgresql.org> (raw)
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 <andrewbille@gmail.com>
Reported-by: Justin Pryzby <pryzby@telsasoft.com>
Discussion: https://postgr.es/m/CAJnzarwkfRu76_yi3dqVF_WL-MpvT54zMwAxFwJceXdHB76bOA@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(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: alvherre@alvh.no-ip.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix restore of not-null constraints with inheritance
In-Reply-To: <E1rxRxO-002hM4-Lk@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox