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

Commit43b8e6c

Browse files
committed
Repair misbehavior with duplicate entries in FK SET column lists.
Since v15 we've had an option to apply a foreign key constraint'sON DELETE SET DEFAULT or SET NULL action to just some of thereferencing columns. There was not a check for duplicate entries inthe list of columns-to-set, though. That caused a potential memorystomp in CreateConstraintEntry(), which incautiously assumed thatthe list of columns-to-set couldn't be longer than the number of keycolumns. Even after fixing that, the case doesn't work because youget an error like "multiple assignments to same column" from the SQLcommand that is generated to do the update.We could either raise an error for duplicate columns or silentlysuppress the dups, and after a bit of thought I chose to do thelatter. This is motivated by the fact that duplicates in the FKcolumn list are legal, so it's not real clear why duplicatesin the columns-to-set list shouldn't be. Of course there's noneed to actually set the column more than once.I left in the fix in CreateConstraintEntry() too, just becauseit didn't seem like such low-level code ought to be makingassumptions about what it's handed.Bug: #18879Reported-by: Yu Liang <luy70@psu.edu>Author: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/18879-259fc59d072bd4d7@postgresql.orgBackpatch-through: 15
1 parent0f43083 commit43b8e6c

File tree

4 files changed

+34
-10
lines changed

4 files changed

+34
-10
lines changed

‎src/backend/catalog/pg_constraint.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -129,8 +129,9 @@ CreateConstraintEntry(const char *constraintName,
129129
if (foreignNKeys>0)
130130
{
131131
Datum*fkdatums;
132+
intnkeys=Max(foreignNKeys,numFkDeleteSetCols);
132133

133-
fkdatums= (Datum*)palloc(foreignNKeys*sizeof(Datum));
134+
fkdatums= (Datum*)palloc(nkeys*sizeof(Datum));
134135
for (i=0;i<foreignNKeys;i++)
135136
fkdatums[i]=Int16GetDatum(foreignKey[i]);
136137
confkeyArray=construct_array_builtin(fkdatums,foreignNKeys,INT2OID);

‎src/backend/commands/tablecmds.c‎

Lines changed: 28 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -557,8 +557,8 @@ static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *
557557
Relation rel, Constraint *fkconstraint,
558558
bool recurse, bool recursing,
559559
LOCKMODE lockmode);
560-
staticvoidvalidateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
561-
int numfksetcols,constint16 *fksetcolsattnums,
560+
staticintvalidateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
561+
int numfksetcols, int16 *fksetcolsattnums,
562562
List *fksetcols);
563563
static ObjectAddress addFkConstraint(addFkConstraintSides fkside,
564564
char *constraintname,
@@ -10037,9 +10037,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
1003710037
numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel),
1003810038
fkconstraint->fk_del_set_cols,
1003910039
fkdelsetcols, NULL, NULL);
10040-
validateFkOnDeleteSetColumns(numfks, fkattnum,
10041-
numfkdelsetcols, fkdelsetcols,
10042-
fkconstraint->fk_del_set_cols);
10040+
numfkdelsetcols = validateFkOnDeleteSetColumns(numfks, fkattnum,
10041+
numfkdelsetcols,
10042+
fkdelsetcols,
10043+
fkconstraint->fk_del_set_cols);
1004310044

1004410045
/*
1004510046
* If the attribute list for the referenced table was omitted, lookup the
@@ -10499,17 +10500,23 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
1049910500
* validateFkOnDeleteSetColumns
1050010501
*Verifies that columns used in ON DELETE SET NULL/DEFAULT (...)
1050110502
*column lists are valid.
10503+
*
10504+
* If there are duplicates in the fksetcolsattnums[] array, this silently
10505+
* removes the dups. The new count of numfksetcols is returned.
1050210506
*/
10503-
void
10507+
static int
1050410508
validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
10505-
int numfksetcols,constint16 *fksetcolsattnums,
10509+
int numfksetcols, int16 *fksetcolsattnums,
1050610510
List *fksetcols)
1050710511
{
10512+
intnumcolsout = 0;
10513+
1050810514
for (int i = 0; i < numfksetcols; i++)
1050910515
{
1051010516
int16setcol_attnum = fksetcolsattnums[i];
1051110517
boolseen = false;
1051210518

10519+
/* Make sure it's in fkattnums[] */
1051310520
for (int j = 0; j < numfks; j++)
1051410521
{
1051510522
if (fkattnums[j] == setcol_attnum)
@@ -10527,7 +10534,21 @@ validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
1052710534
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
1052810535
errmsg("column \"%s\" referenced in ON DELETE SET action must be part of foreign key", col)));
1052910536
}
10537+
10538+
/* Now check for dups */
10539+
seen = false;
10540+
for (int j = 0; j < numcolsout; j++)
10541+
{
10542+
if (fksetcolsattnums[j] == setcol_attnum)
10543+
{
10544+
seen = true;
10545+
break;
10546+
}
10547+
}
10548+
if (!seen)
10549+
fksetcolsattnums[numcolsout++] = setcol_attnum;
1053010550
}
10551+
return numcolsout;
1053110552
}
1053210553

1053310554
/*

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -837,7 +837,8 @@ CREATE TABLE FKTABLE (
837837
fk_id_del_set_null int,
838838
fk_id_del_set_default int DEFAULT 0,
839839
FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES PKTABLE ON DELETE SET NULL (fk_id_del_set_null),
840-
FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default)
840+
-- this tests handling of duplicate entries in SET DEFAULT column list
841+
FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default, fk_id_del_set_default)
841842
);
842843
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass::oid ORDER BY oid;
843844
pg_get_constraintdef

‎src/test/regress/sql/foreign_key.sql‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -517,7 +517,8 @@ CREATE TABLE FKTABLE (
517517
fk_id_del_set_nullint,
518518
fk_id_del_set_defaultint DEFAULT0,
519519
FOREIGN KEY (tid, fk_id_del_set_null)REFERENCES PKTABLEON DELETESETNULL (fk_id_del_set_null),
520-
FOREIGN KEY (tid, fk_id_del_set_default)REFERENCES PKTABLEON DELETESET DEFAULT (fk_id_del_set_default)
520+
-- this tests handling of duplicate entries in SET DEFAULT column list
521+
FOREIGN KEY (tid, fk_id_del_set_default)REFERENCES PKTABLEON DELETESET DEFAULT (fk_id_del_set_default, fk_id_del_set_default)
521522
);
522523

523524
SELECT pg_get_constraintdef(oid)FROM pg_constraintWHERE conrelid='fktable'::regclass::oidORDER BYoid;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp