Skip to content

Commit ade2409

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 dd38ff2 commit ade2409

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

1033910339
/* No dice. Set up to create our own constraint */
1034010340
fkconstraint = makeNode(Constraint);
10341-
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
10342-
RelationGetRelid(partRel),
10343-
NameStr(constrForm->conname)))
10344-
fkconstraint->conname =
10345-
ChooseConstraintName(RelationGetRelationName(partRel),
10346-
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
10347-
"fkey",
10348-
RelationGetNamespace(partRel), NIL);
10349-
else
10350-
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
1035110341
fkconstraint->fk_upd_action = constrForm->confupdtype;
1035210342
fkconstraint->fk_del_action = constrForm->confdeltype;
1035310343
fkconstraint->deferrable = constrForm->condeferrable;
@@ -10362,6 +10352,16 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
1036210352
fkconstraint->fk_attrs = lappend(fkconstraint->fk_attrs,
1036310353
makeString(NameStr(att->attname)));
1036410354
}
10355+
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
10356+
RelationGetRelid(partRel),
10357+
NameStr(constrForm->conname)))
10358+
fkconstraint->conname =
10359+
ChooseConstraintName(RelationGetRelationName(partRel),
10360+
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
10361+
"fkey",
10362+
RelationGetNamespace(partRel), NIL);
10363+
else
10364+
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
1036510365

1036610366
indexOid = constrForm->conindid;
1036710367
constrOid =

‎src/test/regress/expected/constraints.out

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -603,6 +603,29 @@ COMMIT;
603603
ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
604604
DETAIL: Key (i)=(1) already exists.
605605
DROP TABLE parted_uniq_tbl;
606+
-- test naming a constraint in a partition when a conflict exists
607+
CREATE TABLE parted_fk_naming (
608+
id bigint NOT NULL default 1,
609+
id_abc bigint,
610+
CONSTRAINT dummy_constr FOREIGN KEY (id_abc)
611+
REFERENCES parted_fk_naming (id),
612+
PRIMARY KEY (id)
613+
)
614+
PARTITION BY LIST (id);
615+
CREATE TABLE parted_fk_naming_1 (
616+
id bigint NOT NULL default 1,
617+
id_abc bigint,
618+
PRIMARY KEY (id),
619+
CONSTRAINT dummy_constr CHECK (true)
620+
);
621+
ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
622+
SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
623+
conname
624+
--------------------------------
625+
parted_fk_naming_1_id_abc_fkey
626+
(1 row)
627+
628+
DROP TABLE parted_fk_naming;
606629
-- test a HOT update that invalidates the conflicting tuple.
607630
-- the trigger should still fire and catch the violation
608631
BEGIN;

‎src/test/regress/sql/constraints.sql

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

433+
-- test naming a constraint in a partition when a conflict exists
434+
CREATE TABLE parted_fk_naming (
435+
id bigint NOT NULL default 1,
436+
id_abc bigint,
437+
CONSTRAINT dummy_constr FOREIGN KEY (id_abc)
438+
REFERENCES parted_fk_naming (id),
439+
PRIMARY KEY (id)
440+
)
441+
PARTITION BY LIST (id);
442+
CREATE TABLE parted_fk_naming_1 (
443+
id bigint NOT NULL default 1,
444+
id_abc bigint,
445+
PRIMARY KEY (id),
446+
CONSTRAINT dummy_constr CHECK (true)
447+
);
448+
ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
449+
SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
450+
DROP TABLE parted_fk_naming;
451+
433452
-- test a HOT update that invalidates the conflicting tuple.
434453
-- the trigger should still fire and catch the violation
435454

0 commit comments

Comments
 (0)