[BUG] wrong FK constraint name when colliding name on ATTACH
Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-09-01T16:41:56Z
Lists: pgsql-hackers
Attachments
Hi,
While studying and hacking on the parenting constraint issue, I found an
incoherent piece of code leading to badly chosen fk name. If a constraint
name collision is detected, while choosing a new name for the constraint,
the code uses fkconstraint->fk_attrs which is not yet populated:
/* No dice. Set up to create our own constraint */
fkconstraint = makeNode(Constraint);
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
RelationGetRelid(partRel),
NameStr(constrForm->conname)))
fkconstraint->conname =
ChooseConstraintName(RelationGetRelationName(partRel),
ChooseForeignKeyConstraintNameAddition(
fkconstraint->fk_attrs), // <= WOO000OPS
"fkey",
RelationGetNamespace(partRel), NIL);
else
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
fkconstraint->fk_upd_action = constrForm->confupdtype;
fkconstraint->fk_del_action = constrForm->confdeltype;
fkconstraint->deferrable = constrForm->condeferrable;
fkconstraint->initdeferred = constrForm->condeferred;
fkconstraint->fk_matchtype = constrForm->confmatchtype;
for (int i = 0; i < numfks; i++)
{
Form_pg_attribute att;
att = TupleDescAttr(RelationGetDescr(partRel),
mapped_conkey[i] - 1);
fkconstraint->fk_attrs = lappend(fkconstraint->fk_attrs, // <= POPULATING
makeString(NameStr(att->attname)));
}
The following SQL script showcase the bad constraint name:
DROP TABLE IF EXISTS parent, child1;
CREATE TABLE parent (
id bigint NOT NULL default 1,
no_part smallint NOT NULL,
id_abc bigint,
CONSTRAINT dummy_constr FOREIGN KEY (id_abc, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT,
PRIMARY KEY (id, no_part)
)
PARTITION BY LIST (no_part);
CREATE TABLE child1 (
id bigint NOT NULL default 1,
no_part smallint NOT NULL,
id_abc bigint,
PRIMARY KEY (id, no_part),
CONSTRAINT dummy_constr CHECK ((no_part = 1))
);
ALTER TABLE parent ATTACH PARTITION child1 FOR VALUES IN ('1');
SELECT conname
FROM pg_constraint
WHERE conrelid = 'child1'::regclass
AND contype = 'f';
DROP TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
conname
--------------
child1__fkey
(1 row)
The resulting constraint name "child1__fkey" is missing the attributes name the
original code wanted to add. The expected name is "child1_id_abc_no_part_fkey".
Find in attachment a simple fix, moving the name assignation after the
FK attributes are populated.
Regards,
Commits
-
Create FKs properly when attaching table as partition
- c301e1c0c09c 15.1 landed
- b0284bfb1db5 16.0 landed
- ab70b3a52fde 12.13 landed
- 41b6e7c9a32e 13.9 landed
- 18865f4df9ca 14.6 landed
-
Fix self-referencing foreign keys with partitioned tables
- 7d520e68ea1e 13.9 landed
- 669803af04e6 12.13 landed
- 614a406b4ff1 16.0 landed
- 6083132abdd4 15.0 landed
- 483d26930b4c 14.6 landed
-
Fix GetForeignKey*Triggers for self-referential FKs
- 8c848cd4b8e9 16.0 landed
- 68b0da67794e 15.0 landed
-
Choose FK name correctly during partition attachment
- e7936f8b3e57 16.0 landed
- ade2409b18e9 15.0 landed
- 80ef25b1adb1 13.9 landed
- 640c20d6266d 14.6 landed
- 562e100aeeaf 12.13 landed
-
Update SQL features
- 7e367924e33e 15.0 cited
-
Restore the previous semantics of get_constraint_index().
- 641f3dffcdf1 15.0 cited