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

Commit5e6e97f

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

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
@@ -118,8 +118,9 @@ CreateConstraintEntry(const char *constraintName,
118118
if (foreignNKeys>0)
119119
{
120120
Datum*fkdatums;
121+
intnkeys=Max(foreignNKeys,numFkDeleteSetCols);
121122

122-
fkdatums= (Datum*)palloc(foreignNKeys*sizeof(Datum));
123+
fkdatums= (Datum*)palloc(nkeys*sizeof(Datum));
123124
for (i=0;i<foreignNKeys;i++)
124125
fkdatums[i]=Int16GetDatum(foreignKey[i]);
125126
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
@@ -512,8 +512,8 @@ static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *
512512
Relation rel, Constraint *fkconstraint,
513513
bool recurse, bool recursing,
514514
LOCKMODE lockmode);
515-
staticvoidvalidateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
516-
int numfksetcols,constint16 *fksetcolsattnums,
515+
staticintvalidateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
516+
int numfksetcols, int16 *fksetcolsattnums,
517517
List *fksetcols);
518518
static ObjectAddress addFkConstraint(addFkConstraintSides fkside,
519519
char *constraintname,
@@ -9716,9 +9716,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
97169716
numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel),
97179717
fkconstraint->fk_del_set_cols,
97189718
fkdelsetcols, NULL);
9719-
validateFkOnDeleteSetColumns(numfks, fkattnum,
9720-
numfkdelsetcols, fkdelsetcols,
9721-
fkconstraint->fk_del_set_cols);
9719+
numfkdelsetcols = validateFkOnDeleteSetColumns(numfks, fkattnum,
9720+
numfkdelsetcols,
9721+
fkdelsetcols,
9722+
fkconstraint->fk_del_set_cols);
97229723

97239724
/*
97249725
* If the attribute list for the referenced table was omitted, lookup the
@@ -10039,17 +10040,23 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
1003910040
* validateFkOnDeleteSetColumns
1004010041
*Verifies that columns used in ON DELETE SET NULL/DEFAULT (...)
1004110042
*column lists are valid.
10043+
*
10044+
* If there are duplicates in the fksetcolsattnums[] array, this silently
10045+
* removes the dups. The new count of numfksetcols is returned.
1004210046
*/
10043-
void
10047+
static int
1004410048
validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
10045-
int numfksetcols,constint16 *fksetcolsattnums,
10049+
int numfksetcols, int16 *fksetcolsattnums,
1004610050
List *fksetcols)
1004710051
{
10052+
intnumcolsout = 0;
10053+
1004810054
for (int i = 0; i < numfksetcols; i++)
1004910055
{
1005010056
int16setcol_attnum = fksetcolsattnums[i];
1005110057
boolseen = false;
1005210058

10059+
/* Make sure it's in fkattnums[] */
1005310060
for (int j = 0; j < numfks; j++)
1005410061
{
1005510062
if (fkattnums[j] == setcol_attnum)
@@ -10067,7 +10074,21 @@ validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
1006710074
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
1006810075
errmsg("column \"%s\" referenced in ON DELETE SET action must be part of foreign key", col)));
1006910076
}
10077+
10078+
/* Now check for dups */
10079+
seen = false;
10080+
for (int j = 0; j < numcolsout; j++)
10081+
{
10082+
if (fksetcolsattnums[j] == setcol_attnum)
10083+
{
10084+
seen = true;
10085+
break;
10086+
}
10087+
}
10088+
if (!seen)
10089+
fksetcolsattnums[numcolsout++] = setcol_attnum;
1007010090
}
10091+
return numcolsout;
1007110092
}
1007210093

1007310094
/*

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -772,7 +772,8 @@ CREATE TABLE FKTABLE (
772772
fk_id_del_set_null int,
773773
fk_id_del_set_default int DEFAULT 0,
774774
FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES PKTABLE ON DELETE SET NULL (fk_id_del_set_null),
775-
FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default)
775+
-- this tests handling of duplicate entries in SET DEFAULT column list
776+
FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default, fk_id_del_set_default)
776777
);
777778
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass::oid ORDER BY oid;
778779
pg_get_constraintdef

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -473,7 +473,8 @@ CREATE TABLE FKTABLE (
473473
fk_id_del_set_nullint,
474474
fk_id_del_set_defaultint DEFAULT0,
475475
FOREIGN KEY (tid, fk_id_del_set_null)REFERENCES PKTABLEON DELETESETNULL (fk_id_del_set_null),
476-
FOREIGN KEY (tid, fk_id_del_set_default)REFERENCES PKTABLEON DELETESET DEFAULT (fk_id_del_set_default)
476+
-- this tests handling of duplicate entries in SET DEFAULT column list
477+
FOREIGN KEY (tid, fk_id_del_set_default)REFERENCES PKTABLEON DELETESET DEFAULT (fk_id_del_set_default, fk_id_del_set_default)
477478
);
478479

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

0 commit comments

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