From: Jehan-Guillaume de Rorthais Date: Wed, 5 Jul 2023 19:19:40 +0200 Subject: [PATCH v1] Remove useless parted-FK constraints when attaching a partition When a FK is referencing a partitioned table, this FK is parenting as many other "parted-FK" constraints than the referencing side has partitions. Each of these sub-constraints enforce only the UPDATE/DELETE triggers on each referenced partition, but not the check triggers on the referencing partition as this is already handle by the parent constraint. These parted-FK are half-backed on purpose. However when attaching such standalone table to a partitioned table having the same FK definition, all these sub-constraints were not removed. This leave a useless action trigger on each referenced partition, the legitimate one already checking against the root of the referencing partition. Moreover, when the partition is later detached, DetachPartitionFinalize() look for all existing FK on the relation and try to detach them from the parent triggers and constraints. But because these parted-FK are half backed, calling GetForeignKeyCheckTriggers() to later detach the check triggers raise an ERROR: ERROR: could not find ON INSERT check triggers of foreign key constraint NNNNN. --- src/backend/commands/tablecmds.c | 57 +++++++++++++++++++++-- src/test/regress/expected/foreign_key.out | 22 +++++++++ src/test/regress/sql/foreign_key.sql | 27 +++++++++++ 3 files changed, 103 insertions(+), 3 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index fce5e6f220..7c1aa5d395 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10566,9 +10566,11 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk, Form_pg_constraint parentConstr; HeapTuple partcontup; Form_pg_constraint partConstr; - ScanKeyData key; + ScanKeyData key[3]; SysScanDesc scan; HeapTuple trigtup; + HeapTuple contup; + Relation pg_constraint; Oid insertTriggerOid, updateTriggerOid; @@ -10631,12 +10633,12 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk, * in the partition. We identify them because they have our constraint * OID, as well as being on the referenced rel. */ - ScanKeyInit(&key, + ScanKeyInit(key, Anum_pg_trigger_tgconstraint, BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(fk->conoid)); scan = systable_beginscan(trigrel, TriggerConstraintIndexId, true, - NULL, 1, &key); + NULL, 1, key); while ((trigtup = systable_getnext(scan)) != NULL) { Form_pg_trigger trgform = (Form_pg_trigger) GETSTRUCT(trigtup); @@ -10684,6 +10686,55 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk, TriggerSetParentTrigger(trigrel, updateTriggerOid, parentUpdTrigger, partRelid); + /* + * If the referenced side of the FK is partionned, we need to remove the + * action triggers from these partitions as well as a legitimate one + * already exists and points to the root of the referencing partition. + * These action triggers to remove are binded to a FK constraints having + * this constraint as parent. Dropping these constraints cleans up + * everything. + */ + pg_constraint = table_open(ConstraintRelationId, RowShareLock); + ScanKeyInit(&key[0], + Anum_pg_constraint_conrelid, BTEqualStrategyNumber, + F_OIDEQ, ObjectIdGetDatum(fk->conrelid)); + ScanKeyInit(&key[1], + Anum_pg_constraint_conparentid, BTEqualStrategyNumber, + F_CHAREQ, ObjectIdGetDatum(fk->conoid)); + ScanKeyInit(&key[2], + Anum_pg_constraint_contype, BTEqualStrategyNumber, + F_CHAREQ, CharGetDatum(CONSTRAINT_FOREIGN)); + scan = systable_beginscan(pg_constraint, InvalidOid, true, + NULL, 2, key); + + while ((contup = systable_getnext(scan)) != NULL) + { + ObjectAddress conobj; + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(contup); + + /* + * we need to remove the dependency between this old half-backend + * foreign key constraint and the main one so we can later drop + * it. + */ + deleteDependencyRecordsFor(ConstraintRelationId, + con->oid, + false); + + /* make dependency deletion visible to performDeletion */ + CommandCounterIncrement(); + + conobj.classId = ConstraintRelationId; + conobj.objectId = con->oid; + conobj.objectSubId = 0; + + /* drop the old constraint */ + performDeletion(&conobj, DROP_CASCADE, 0); + } + systable_endscan(scan); + table_close(pg_constraint, RowShareLock); + + CommandCounterIncrement(); return true; } diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 55f7158c1a..1595739cf7 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -2917,3 +2917,25 @@ DETAIL: drop cascades to table fkpart11.pk drop cascades to table fkpart11.fk_parted drop cascades to table fkpart11.fk_another drop cascades to function fkpart11.print_row() +-- Test partitioned tables on both ends of the referential constraint +-- test attaching and detaching a partition with an existing +-- foreign key constraint +BEGIN; +CREATE TABLE parted_refd ( + id bigint, + PRIMARY KEY (id) +) +PARTITION BY list (id); +CREATE TABLE parted_refd_1 PARTITION OF parted_refd FOR VALUES IN (1); +CREATE TABLE parted_refg ( + id bigint, + p_id bigint NOT NULL, + PRIMARY KEY (id), + FOREIGN KEY (p_id) REFERENCES parted_refd (id) +) +PARTITION BY list (id); +CREATE TABLE parted_refg_1 ( LIKE parted_refg INCLUDING ALL ); +ALTER TABLE parted_refg_1 ADD FOREIGN KEY (p_id) REFERENCES parted_refd (id); +ALTER TABLE parted_refg ATTACH PARTITION parted_refg_1 FOR VALUES IN (1); +ALTER TABLE parted_refg DETACH PARTITION parted_refg_1; +ROLLBACK; diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 22e177f89b..02828d3e94 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -2069,3 +2069,30 @@ UPDATE fkpart11.pk SET a = 3 WHERE a = 4; UPDATE fkpart11.pk SET a = 1 WHERE a = 2; DROP SCHEMA fkpart11 CASCADE; + +-- Test partitioned tables on both ends of the referential constraint + +-- test attaching and detaching a partition with an existing +-- foreign key constraint +BEGIN; +CREATE TABLE parted_refd ( + id bigint, + PRIMARY KEY (id) +) +PARTITION BY list (id); +CREATE TABLE parted_refd_1 PARTITION OF parted_refd FOR VALUES IN (1); + +CREATE TABLE parted_refg ( + id bigint, + p_id bigint NOT NULL, + PRIMARY KEY (id), + FOREIGN KEY (p_id) REFERENCES parted_refd (id) +) +PARTITION BY list (id); + +CREATE TABLE parted_refg_1 ( LIKE parted_refg INCLUDING ALL ); +ALTER TABLE parted_refg_1 ADD FOREIGN KEY (p_id) REFERENCES parted_refd (id); + +ALTER TABLE parted_refg ATTACH PARTITION parted_refg_1 FOR VALUES IN (1); +ALTER TABLE parted_refg DETACH PARTITION parted_refg_1; +ROLLBACK; -- 2.40.1 --MP_/Kkn0GAMPatwoFL2oJJ/2V3a--