Skip to content

Commit 562e100

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 4d7c0fe commit 562e100

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
@@ -9177,16 +9177,6 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
91779177

91789178
/* No dice. Set up to create our own constraint */
91799179
fkconstraint = makeNode(Constraint);
9180-
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
9181-
RelationGetRelid(partRel),
9182-
NameStr(constrForm->conname)))
9183-
fkconstraint->conname =
9184-
ChooseConstraintName(RelationGetRelationName(partRel),
9185-
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
9186-
"fkey",
9187-
RelationGetNamespace(partRel), NIL);
9188-
else
9189-
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
91909180
fkconstraint->fk_upd_action = constrForm->confupdtype;
91919181
fkconstraint->fk_del_action = constrForm->confdeltype;
91929182
fkconstraint->deferrable = constrForm->condeferrable;
@@ -9201,6 +9191,16 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
92019191
fkconstraint->fk_attrs = lappend(fkconstraint->fk_attrs,
92029192
makeString(NameStr(att->attname)));
92039193
}
9194+
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
9195+
RelationGetRelid(partRel),
9196+
NameStr(constrForm->conname)))
9197+
fkconstraint->conname =
9198+
ChooseConstraintName(RelationGetRelationName(partRel),
9199+
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
9200+
"fkey",
9201+
RelationGetNamespace(partRel), NIL);
9202+
else
9203+
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
92049204

92059205
indexOid = constrForm->conindid;
92069206
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)