From: Álvaro Herrera <alvherre@alvh.no-ip.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Elide not-null constraint checks on child tables during PK creat
Date: Wed, 16 Apr 2025 20:02:33 +0000
Message-ID: <E1u58xt-000NUi-05@gemulon.postgresql.org> (raw)
Elide not-null constraint checks on child tables during PK creation
We were unnecessarily acquiring AccessExclusiveLock on all child tables
when "ALTER TABLE ONLY sometab ADD PRIMARY KEY" was run on their parent
table, an oversight in commit 14e87ffa5c54. This caused deadlocks
during pg_restore of partitioned tables.
The reason to acquire the AEL was that we need to verify that child
tables have the involved columns already marked as not-null; but if the
parent table has an inheritable not-null constraint, then all children
must necessarily be in the correct state already, so we can skip the
check, which avoids acquiring the lock. Reorder the code so that it
works that way. This doesn't change things in the case where the
constraint doesn't exist, but that case is of lesser importance because
it doesn't occur during parallel pg_restore.
While at it, reword some errmsg() and add errhint() to similar cases in
related but not adjacent code.
Diagnosed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Discussion: https://postgr.es/m/67469c1c-38bc-7d94-918a-67033f5dd731@gmx.net
Discussion: https://postgr.es/m/2045026.1743801143@sss.pgh.pa.us
Discussion: https://postgr.es/m/1280408.1744650810@sss.pgh.pa.us
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/11ff192b5bb707ba9ec13a0b6c7468874403abb3
Modified Files
--------------
src/backend/catalog/pg_constraint.c | 7 +-
src/backend/commands/tablecmds.c | 145 ++++++++++++++++++------------
src/test/regress/expected/constraints.out | 32 +++++--
src/test/regress/expected/inherit.out | 3 +
src/test/regress/sql/constraints.sql | 14 +++
5 files changed, 136 insertions(+), 65 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: Elide not-null constraint checks on child tables during PK creat
In-Reply-To: <E1u58xt-000NUi-05@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