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

Commitade2409

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 parentdd38ff2 commitade2409

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