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