Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit80ef25b

Browse files
committed
Choose FK name correctly during partition attachment
During ALTER TABLE ATTACH PARTITION, if the name of a parent's foreignkey constraint is already used on the partition, the code tries tochoose 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 smartenough to cope with conflicting constraint names, so the problem doesn'texist there.Author: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>Discussion:https://postgr.es/m/20220901184156.738ebee5@karst
1 parentccbb54c commit80ef25b

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

96329632
/* No dice. Set up to create our own constraint */
96339633
fkconstraint = makeNode(Constraint);
9634-
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
9635-
RelationGetRelid(partRel),
9636-
NameStr(constrForm->conname)))
9637-
fkconstraint->conname =
9638-
ChooseConstraintName(RelationGetRelationName(partRel),
9639-
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
9640-
"fkey",
9641-
RelationGetNamespace(partRel), NIL);
9642-
else
9643-
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
96449634
fkconstraint->fk_upd_action = constrForm->confupdtype;
96459635
fkconstraint->fk_del_action = constrForm->confdeltype;
96469636
fkconstraint->deferrable = constrForm->condeferrable;
@@ -9655,6 +9645,16 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
96559645
fkconstraint->fk_attrs = lappend(fkconstraint->fk_attrs,
96569646
makeString(NameStr(att->attname)));
96579647
}
9648+
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
9649+
RelationGetRelid(partRel),
9650+
NameStr(constrForm->conname)))
9651+
fkconstraint->conname =
9652+
ChooseConstraintName(RelationGetRelationName(partRel),
9653+
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
9654+
"fkey",
9655+
RelationGetNamespace(partRel), NIL);
9656+
else
9657+
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
96589658

96599659
indexOid = constrForm->conindid;
96609660
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)
[8]ページ先頭
©2009-2025 Movatter.jp