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

Commit136afa7

Browse files
alvherrepull[bot]
authored andcommitted
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 parentdb35255 commit136afa7

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

1030510305
/* No dice. Set up to create our own constraint */
1030610306
fkconstraint = makeNode(Constraint);
10307-
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
10308-
RelationGetRelid(partRel),
10309-
NameStr(constrForm->conname)))
10310-
fkconstraint->conname =
10311-
ChooseConstraintName(RelationGetRelationName(partRel),
10312-
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
10313-
"fkey",
10314-
RelationGetNamespace(partRel), NIL);
10315-
else
10316-
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
1031710307
fkconstraint->fk_upd_action = constrForm->confupdtype;
1031810308
fkconstraint->fk_del_action = constrForm->confdeltype;
1031910309
fkconstraint->deferrable = constrForm->condeferrable;
@@ -10328,6 +10318,16 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
1032810318
fkconstraint->fk_attrs = lappend(fkconstraint->fk_attrs,
1032910319
makeString(NameStr(att->attname)));
1033010320
}
10321+
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
10322+
RelationGetRelid(partRel),
10323+
NameStr(constrForm->conname)))
10324+
fkconstraint->conname =
10325+
ChooseConstraintName(RelationGetRelationName(partRel),
10326+
ChooseForeignKeyConstraintNameAddition(fkconstraint->fk_attrs),
10327+
"fkey",
10328+
RelationGetNamespace(partRel), NIL);
10329+
else
10330+
fkconstraint->conname = pstrdup(NameStr(constrForm->conname));
1033110331

1033210332
indexOid = constrForm->conindid;
1033310333
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
DROPTABLE parted_uniq_tbl;
432432

433+
-- test naming a constraint in a partition when a conflict exists
434+
CREATETABLEparted_fk_naming (
435+
idbigintNOT NULL default1,
436+
id_abcbigint,
437+
CONSTRAINT dummy_constrFOREIGN KEY (id_abc)
438+
REFERENCES parted_fk_naming (id),
439+
PRIMARY KEY (id)
440+
)
441+
PARTITION BY LIST (id);
442+
CREATETABLEparted_fk_naming_1 (
443+
idbigintNOT NULL default1,
444+
id_abcbigint,
445+
PRIMARY KEY (id),
446+
CONSTRAINT dummy_constrCHECK (true)
447+
);
448+
ALTERTABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FORVALUESIN ('1');
449+
SELECT connameFROM pg_constraintWHERE conrelid='parted_fk_naming_1'::regclassAND contype='f';
450+
DROPTABLE 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)
[8]ページ先頭
©2009-2025 Movatter.jp