pgsql: Correct attach/detach logic for FKs in partitions

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: pgsql-committers(at)lists(dot)postgresql(dot)org
Subject: pgsql: Correct attach/detach logic for FKs in partitions
Date: 2018-10-12 15:38:45
Message-ID: E1gAzWX-0002Nq-4g@gemulon.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Correct attach/detach logic for FKs in partitions

There was no code to handle foreign key constraints on partitioned
tables in the case of ALTER TABLE DETACH; and if you happened to ATTACH
a partition that already had an equivalent constraint, that one was
ignored and a new constraint was created. Adding this to the fact that
foreign key cloning reuses the constraint name on the partition instead
of generating a new name (as it probably should, to cater to SQL
standard rules about constraint naming within schemas), the result was a
pretty poor user experience -- the most visible failure was that just
detaching a partition and re-attaching it failed with an error such as

ERROR: duplicate key value violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(26702, 0, test_result_asset_id_fkey) already exists.

because it would try to create an identically-named constraint in the
partition. To make matters worse, if you tried to drop the constraint
in the now-independent partition, that would fail because the constraint
was still seen as dependent on the constraint in its former parent
partitioned table:
ERROR: cannot drop inherited constraint "test_result_asset_id_fkey" of relation "test_result_cbsystem_0001_0050_monthly_2018_09"

This fix attacks the problem from two angles: first, when the partition
is detached, the constraint is also marked as independent, so the drop
now works. Second, when the partition is re-attached, we scan existing
constraints searching for one matching the FK in the parent, and if one
exists, we link that one to the parent constraint. So we don't end up
with a duplicate -- and better yet, we don't need to scan the referenced
table to verify that the constraint holds.

To implement this I made a small change to previously planner-only
struct ForeignKeyCacheInfo to contain the constraint OID; also relcache
now maintains the list of FKs for partitioned tables too.

Backpatch to 11.

Reported-by: Michael Vitale (bug #15425)
Discussion: https://postgr.es/m/15425-2dbc9d2aa999f816@postgresql.org

Branch
------
REL_11_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/355684ee3fa3001adf0de3873fddadd119773819

Modified Files
--------------
src/backend/catalog/pg_constraint.c | 238 +++++++++++++++++++++++++-----
src/backend/commands/tablecmds.c | 23 +++
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/utils/cache/relcache.c | 6 +-
src/include/utils/rel.h | 5 +-
src/test/regress/expected/foreign_key.out | 119 +++++++++++++++
src/test/regress/sql/foreign_key.sql | 50 +++++++
8 files changed, 404 insertions(+), 39 deletions(-)

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2018-10-12 15:42:39 pgsql: Remove dead reference to ecpg resultmap file.
Previous Message Tom Lane 2018-10-12 15:15:11 pgsql: Make float exponent output on Windows look the same as elsewhere