Skip to content

Commit 640c20d

Browse files
alvherrealvherre
committed
Choose FK name correctly during partition attachment
During ALTER TABLE ATTACH PARTITION, if the name of a parent's foreign key constraint is already used on the partition, the code tries to choose another one before the FK attributes list has been populated, so the resulting constraint name was "<relname>__fkey" instead of "<relname>_<attrs>_fkey". Repair, and add a test case. Backpatch to 12. In 11, the code to attach a partition was not smart enough to cope with conflicting constraint names, so the problem doesn't exist there. Author: Jehan-Guillaume de Rorthais <jgdr@dalibo.com> Discussion: https://postgr.es/m/20220901184156.738ebee5@karst
1 parent a254545 commit 640c20d

File tree

3 files changed

+52
-10
lines changed

3 files changed

+52
-10
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -10024,16 +10024,6 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
1002410024

1002510025
/* No dice. Set up to create our own constraint */
1002610026
fkconstraint = makeNode(Constraint);
10027-
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
10028-
RelationGetRelid(partRel),
10029-
NameStr(constrForm->conname)))
10030-
fkconstraint->conname =
10031-
ChooseConstraintName(RelationGetRelationName(partRel),
10032-
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
10033-
"fkey",
10034-
RelationGetNamespace(partRel), NIL);
10035-
else
10036-
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
1003710027
fkconstraint->fk_upd_action = constrForm->confupdtype;
1003810028
fkconstraint->fk_del_action = constrForm->confdeltype;
1003910029
fkconstraint->deferrable = constrForm->condeferrable;
@@ -10048,6 +10038,16 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
1004810038
fkconstraint->fk_attrs = lappend(fkconstraint->fk_attrs,
1004910039
makeString(NameStr(att->attname)));
1005010040
}
10041+
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
10042+
RelationGetRelid(partRel),
10043+
NameStr(constrForm->conname)))
10044+
fkconstraint->conname =
10045+
ChooseConstraintName(RelationGetRelationName(partRel),
10046+
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
10047+
"fkey",
10048+
RelationGetNamespace(partRel), NIL);
10049+
else
10050+
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
1005110051

1005210052
indexOid = constrForm->conindid;
1005310053
constrOid =

‎src/test/regress/input/constraints.source

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -410,6 +410,25 @@ INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit
410410
COMMIT;
411411
DROP TABLE parted_uniq_tbl;
412412

413+
-- test naming a constraint in a partition when a conflict exists
414+
CREATE TABLE parted_fk_naming (
415+
id bigint NOT NULL default 1,
416+
id_abc bigint,
417+
CONSTRAINT dummy_constr FOREIGN KEY (id_abc)
418+
REFERENCES parted_fk_naming (id),
419+
PRIMARY KEY (id)
420+
)
421+
PARTITION BY LIST (id);
422+
CREATE TABLE parted_fk_naming_1 (
423+
id bigint NOT NULL default 1,
424+
id_abc bigint,
425+
PRIMARY KEY (id),
426+
CONSTRAINT dummy_constr CHECK (true)
427+
);
428+
ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
429+
SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
430+
DROP TABLE parted_fk_naming;
431+
413432
-- test a HOT update that invalidates the conflicting tuple.
414433
-- the trigger should still fire and catch the violation
415434

‎src/test/regress/output/constraints.source

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -575,6 +575,29 @@ COMMIT;
575575
ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
576576
DETAIL: Key (i)=(1) already exists.
577577
DROP TABLE parted_uniq_tbl;
578+
-- test naming a constraint in a partition when a conflict exists
579+
CREATE TABLE parted_fk_naming (
580+
id bigint NOT NULL default 1,
581+
id_abc bigint,
582+
CONSTRAINT dummy_constr FOREIGN KEY (id_abc)
583+
REFERENCES parted_fk_naming (id),
584+
PRIMARY KEY (id)
585+
)
586+
PARTITION BY LIST (id);
587+
CREATE TABLE parted_fk_naming_1 (
588+
id bigint NOT NULL default 1,
589+
id_abc bigint,
590+
PRIMARY KEY (id),
591+
CONSTRAINT dummy_constr CHECK (true)
592+
);
593+
ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
594+
SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
595+
conname
596+
--------------------------------
597+
parted_fk_naming_1_id_abc_fkey
598+
(1 row)
599+
600+
DROP TABLE parted_fk_naming;
578601
-- test a HOT update that invalidates the conflicting tuple.
579602
-- the trigger should still fire and catch the violation
580603
BEGIN;

0 commit comments

Comments
 (0)