public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jehan-Guillaume de Rorthais <[email protected]>
Subject: [PATCH v1] Remove useless parted-FK constraints when attaching a partition
Date: Wed, 5 Jul 2023 19:19:40 +0200
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--
view thread (3+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v1] Remove useless parted-FK constraints when attaching a partition
In-Reply-To: <no-message-id-1856799@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox