Skip to content

Commit f0d65c0

Browse files
tglsfdctglsfdc
committed
Reject system columns as elements of foreign keys.
Up through v11 it was sensible to use the "oid" system column as a foreign key column, but since that was removed there's no visible usefulness in making any of the remaining system columns a foreign key. Moreover, since the TupleTableSlot rewrites in v12, such cases actively fail because of implicit assumptions that only user columns appear in foreign keys. The lack of complaints about that seems like good evidence that no one is trying to do it. Hence, rather than trying to repair those assumptions (of which there are at least two, maybe more), let's just forbid the case up front. Per this patch, a system column in either the referenced or referencing side of a foreign key will draw this error; however, putting one in the referenced side would have failed later anyway, since we don't allow unique indexes to be made on system columns. Per bug #17877 from Alexander Lakhin. Back-patch to v12; the case still appears to work in v11, so we shouldn't break it there. Discussion: https://postgr.es/m/17877-4bcc658e33df6de1@postgresql.org
1 parent c2d7d67 commit f0d65c0

File tree

3 files changed

+23
-10
lines changed

3 files changed

+23
-10
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11271,6 +11271,11 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
1127111271
* transformColumnNameList - transform list of column names
1127211272
*
1127311273
* Lookup each name and return its attnum and, optionally, type OID
11274+
*
11275+
* Note: the name of this function suggests that it's general-purpose,
11276+
* but actually it's only used to look up names appearing in foreign-key
11277+
* clauses. The error messages would need work to use it in other cases,
11278+
* and perhaps the validity checks as well.
1127411279
*/
1127511280
static int
1127611281
transformColumnNameList(Oid relId, List *colList,
@@ -11284,21 +11289,27 @@ transformColumnNameList(Oid relId, List *colList,
1128411289
{
1128511290
char *attname = strVal(lfirst(l));
1128611291
HeapTuple atttuple;
11292+
Form_pg_attribute attform;
1128711293

1128811294
atttuple = SearchSysCacheAttName(relId, attname);
1128911295
if (!HeapTupleIsValid(atttuple))
1129011296
ereport(ERROR,
1129111297
(errcode(ERRCODE_UNDEFINED_COLUMN),
1129211298
errmsg("column \"%s\" referenced in foreign key constraint does not exist",
1129311299
attname)));
11300+
attform = (Form_pg_attribute) GETSTRUCT(atttuple);
11301+
if (attform->attnum < 0)
11302+
ereport(ERROR,
11303+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
11304+
errmsg("system columns cannot be used in foreign keys")));
1129411305
if (attnum >= INDEX_MAX_KEYS)
1129511306
ereport(ERROR,
1129611307
(errcode(ERRCODE_TOO_MANY_COLUMNS),
1129711308
errmsg("cannot have more than %d keys in a foreign key",
1129811309
INDEX_MAX_KEYS)));
11299-
attnums[attnum] = ((Form_pg_attribute) GETSTRUCT(atttuple))->attnum;
11310+
attnums[attnum] = attform->attnum;
1130011311
if (atttypids != NULL)
11301-
atttypids[attnum] = ((Form_pg_attribute) GETSTRUCT(atttuple))->atttypid;
11312+
atttypids[attnum] = attform->atttypid;
1130211313
ReleaseSysCache(atttuple);
1130311314
attnum++;
1130411315
}

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

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -795,15 +795,16 @@ SELECT * FROM FKTABLE ORDER BY id;
795795

796796
DROP TABLE FKTABLE;
797797
DROP TABLE PKTABLE;
798-
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
798+
-- Test some invalid FK definitions
799+
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY, someoid oid);
799800
CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
800801
ERROR: column "ftest2" referenced in foreign key constraint does not exist
801802
CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
802803
ERROR: column "ptest2" referenced in foreign key constraint does not exist
803-
DROP TABLE FKTABLE_FAIL1;
804-
ERROR: table "fktable_fail1" does not exist
805-
DROP TABLE FKTABLE_FAIL2;
806-
ERROR: table "fktable_fail2" does not exist
804+
CREATE TABLE FKTABLE_FAIL3 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (tableoid) REFERENCES PKTABLE(someoid));
805+
ERROR: system columns cannot be used in foreign keys
806+
CREATE TABLE FKTABLE_FAIL4 ( ftest1 oid, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(tableoid));
807+
ERROR: system columns cannot be used in foreign keys
807808
DROP TABLE PKTABLE;
808809
-- Test for referencing column number smaller than referenced constraint
809810
CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));

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

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -490,12 +490,13 @@ SELECT * FROM FKTABLE ORDER BY id;
490490
DROP TABLE FKTABLE;
491491
DROP TABLE PKTABLE;
492492

493-
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
493+
-- Test some invalid FK definitions
494+
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY, someoid oid);
494495
CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
495496
CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
497+
CREATE TABLE FKTABLE_FAIL3 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (tableoid) REFERENCES PKTABLE(someoid));
498+
CREATE TABLE FKTABLE_FAIL4 ( ftest1 oid, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(tableoid));
496499

497-
DROP TABLE FKTABLE_FAIL1;
498-
DROP TABLE FKTABLE_FAIL2;
499500
DROP TABLE PKTABLE;
500501

501502
-- Test for referencing column number smaller than referenced constraint

0 commit comments

Comments
 (0)