From 146f40285ee5f773f68205f1cbafe1cbec46c5c4 Mon Sep 17 00:00:00 2001 From: Jehan-Guillaume de Rorthais Date: Fri, 30 Sep 2022 23:42:21 +0200 Subject: [PATCH 1/3] Fix bug between constraint parenting and self-FK Function get_relation_idx_constraint_oid() assumed an index can only be associated to zero or one constraint for a given relation. However, if the relation has a self-foreign key, the index could be referenced as enforcing two different constraints on the same relation: the PK and the FK. This lead to a bug with partitionned table where the self-FK could become the parent of a partition's PK. --- src/backend/catalog/pg_constraint.c | 16 ++++++++++++++-- 1 file changed, 14 insertions(+), 2 deletions(-) diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index bb65fb1e0a..be26dbe81d 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -985,8 +985,8 @@ get_relation_constraint_attnos(Oid relid, const char *conname, } /* - * Return the OID of the constraint associated with the given index in the - * given relation; or InvalidOid if no such index is catalogued. + * Return the OID of the original constraint enforced by the given index + * in the given relation; or InvalidOid if no such index is catalogued. */ Oid get_relation_idx_constraint_oid(Oid relationId, Oid indexId) @@ -1011,6 +1011,18 @@ get_relation_idx_constraint_oid(Oid relationId, Oid indexId) Form_pg_constraint constrForm; constrForm = (Form_pg_constraint) GETSTRUCT(tuple); + + /* + * Self-Foreign keys are ignored as the index was preliminary created + * to enforce a PK or unique constraint in the first place. This means + * only primary key, unique constraint or an exlusion one can be + * returned. + */ + if (constrForm->contype != CONSTRAINT_PRIMARY + && constrForm->contype != CONSTRAINT_UNIQUE + && constrForm->contype != CONSTRAINT_EXCLUSION) + continue; + if (constrForm->conindid == indexId) { constraintId = constrForm->oid; -- 2.37.3