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

Commit640c20d

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 parenta254545 commit640c20d

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)
[8]ページ先頭
©2009-2025 Movatter.jp