I think the code to determine that fk of a partition is inherited or not is
not enough.
For example, in this case, foreign key r_1_p_id_fkey1 is not inherited
from parent.
If conform->conparentid(in DetachPartitionFinalize func) is valid, we
should recheck confrelid(pg_constraint) field.
I try to fix this problem in the attached patch.
Any thoughts.
Alvaro Herrera <alvhe...@alvh.no-ip.org> 于2023年8月3日周四 17:02写道：
> On 2023-Aug-03, tender wang wrote:
>
> > I think old "sub-FK" should not be dropped, that will be violates
> foreign
> > key constraint.
>
> Yeah, I've been playing more with the patch and it is definitely not
> doing the right things. Just eyeballing the contents of pg_trigger and
> pg_constraint for partitions added by ALTER...ATTACH shows that the
> catalog contents are inconsistent with those added by CREATE TABLE
> PARTITION OF.
>
> --
> Álvaro Herrera PostgreSQL Developer —
> https://www.EnterpriseDB.com/
>
From d84395c7321c201a78661de0b41b76e71ab10678 Mon Sep 17 00:00:00 2001
From: "tender.wang" <tender.w...@openpie.com>
Date: Thu, 3 Aug 2023 17:23:06 +0800
Subject: [PATCH] Recheck foreign key of a partition is inherited from parent.
Previously, fk is inherited if conparentid(pg_constraint) is valid.
It is not enough and we should compare confrelid field to determine
fk is inherited.
---
src/backend/commands/tablecmds.c | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 727f151750..1447433109 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -18556,6 +18556,8 @@ DetachPartitionFinalize(Relation rel, Relation partRel,
bool concurrent,
ForeignKeyCacheInfo *fk = lfirst(cell);
HeapTuple contup;
Form_pg_constraint conform;
+ HeapTuple parentTup;
+ Form_pg_constraint parentForm;
Constraint *fkconstraint;
Oid insertTriggerOid,
updateTriggerOid;
@@ -18573,6 +18575,23 @@ DetachPartitionFinalize(Relation rel, Relation
partRel, bool concurrent,
continue;
}
+ /* recheck confrelid field */
+ if (OidIsValid(conform->conparentid))
+ {
+ parentTup = SearchSysCache1(CONSTROID,
ObjectIdGetDatum(conform->conparentid));
+ if (!HeapTupleIsValid(parentTup))
+ elog(ERROR, "cache lookup failed for constraint
%u", conform->conparentid);
+ parentForm = (Form_pg_constraint) GETSTRUCT(parentTup);
+ /* It is not inherited foreign keys */
+ if (parentForm->confrelid != conform->confrelid)
+ {
+ ReleaseSysCache(contup);
+ ReleaseSysCache(parentTup);
+ continue;
+ }
+ ReleaseSysCache(parentTup);
+ }
+
/* unset conparentid and adjust conislocal, coninhcount, etc. */
ConstraintSetParentConstraint(fk->conoid, InvalidOid,
InvalidOid);
--
2.25.1
