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

Commitb0b55d8

Browse files
committed
Reject system columns as elements of foreign keys.
Up through v11 it was sensible to use the "oid" system column asa foreign key column, but since that was removed there's no visibleusefulness in making any of the remaining system columns a foreignkey. Moreover, since the TupleTableSlot rewrites in v12, such casesactively fail because of implicit assumptions that only user columnsappear in foreign keys. The lack of complaints about that seemslike good evidence that no one is trying to do it. Hence, ratherthan trying to repair those assumptions (of which there are at leasttwo, maybe more), let's just forbid the case up front.Per this patch, a system column in either the referenced orreferencing 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; thecase still appears to work in v11, so we shouldn't break it there.Discussion:https://postgr.es/m/17877-4bcc658e33df6de1@postgresql.org
1 parent2dcd92e commitb0b55d8

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
@@ -10800,6 +10800,11 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
1080010800
* transformColumnNameList - transform list of column names
1080110801
*
1080210802
* Lookup each name and return its attnum and type OID
10803+
*
10804+
* Note: the name of this function suggests that it's general-purpose,
10805+
* but actually it's only used to look up names appearing in foreign-key
10806+
* clauses. The error messages would need work to use it in other cases,
10807+
* and perhaps the validity checks as well.
1080310808
*/
1080410809
static int
1080510810
transformColumnNameList(Oid relId, List *colList,
@@ -10813,20 +10818,26 @@ transformColumnNameList(Oid relId, List *colList,
1081310818
{
1081410819
char *attname = strVal(lfirst(l));
1081510820
HeapTupleatttuple;
10821+
Form_pg_attribute attform;
1081610822

1081710823
atttuple = SearchSysCacheAttName(relId, attname);
1081810824
if (!HeapTupleIsValid(atttuple))
1081910825
ereport(ERROR,
1082010826
(errcode(ERRCODE_UNDEFINED_COLUMN),
1082110827
errmsg("column \"%s\" referenced in foreign key constraint does not exist",
1082210828
attname)));
10829+
attform = (Form_pg_attribute) GETSTRUCT(atttuple);
10830+
if (attform->attnum < 0)
10831+
ereport(ERROR,
10832+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
10833+
errmsg("system columns cannot be used in foreign keys")));
1082310834
if (attnum >= INDEX_MAX_KEYS)
1082410835
ereport(ERROR,
1082510836
(errcode(ERRCODE_TOO_MANY_COLUMNS),
1082610837
errmsg("cannot have more than %d keys in a foreign key",
1082710838
INDEX_MAX_KEYS)));
10828-
attnums[attnum] =((Form_pg_attribute) GETSTRUCT(atttuple))->attnum;
10829-
atttypids[attnum] =((Form_pg_attribute) GETSTRUCT(atttuple))->atttypid;
10839+
attnums[attnum] =attform->attnum;
10840+
atttypids[attnum] =attform->atttypid;
1083010841
ReleaseSysCache(atttuple);
1083110842
attnum++;
1083210843
}

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

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -757,15 +757,16 @@ SELECT * from FKTABLE;
757757

758758
DROP TABLE FKTABLE;
759759
DROP TABLE PKTABLE;
760-
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
760+
-- Test some invalid FK definitions
761+
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY, someoid oid);
761762
CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
762763
ERROR: column "ftest2" referenced in foreign key constraint does not exist
763764
CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
764765
ERROR: column "ptest2" referenced in foreign key constraint does not exist
765-
DROP TABLEFKTABLE_FAIL1;
766-
ERROR:table "fktable_fail1" does not exist
767-
DROP TABLEFKTABLE_FAIL2;
768-
ERROR:table "fktable_fail2" does not exist
766+
CREATE TABLEFKTABLE_FAIL3 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (tableoid) REFERENCES PKTABLE(someoid));
767+
ERROR:system columns cannot be used in foreign keys
768+
CREATE TABLEFKTABLE_FAIL4 ( ftest1 oid, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(tableoid));
769+
ERROR:system columns cannot be used in foreign keys
769770
DROP TABLE PKTABLE;
770771
-- Test for referencing column number smaller than referenced constraint
771772
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
@@ -463,12 +463,13 @@ SELECT * from FKTABLE;
463463
DROPTABLE FKTABLE;
464464
DROPTABLE PKTABLE;
465465

466-
CREATETABLEPKTABLE (ptest1intPRIMARY KEY);
466+
-- Test some invalid FK definitions
467+
CREATETABLEPKTABLE (ptest1intPRIMARY KEY, someoidoid);
467468
CREATETABLEFKTABLE_FAIL1 ( ftest1int,CONSTRAINT fkfail1FOREIGN KEY (ftest2)REFERENCES PKTABLE);
468469
CREATETABLEFKTABLE_FAIL2 ( ftest1int,CONSTRAINT fkfail1FOREIGN KEY (ftest1)REFERENCES PKTABLE(ptest2));
470+
CREATETABLEFKTABLE_FAIL3 ( ftest1int,CONSTRAINT fkfail1FOREIGN KEY (tableoid)REFERENCES PKTABLE(someoid));
471+
CREATETABLEFKTABLE_FAIL4 ( ftest1oid,CONSTRAINT fkfail1FOREIGN KEY (ftest1)REFERENCES PKTABLE(tableoid));
469472

470-
DROPTABLE FKTABLE_FAIL1;
471-
DROPTABLE FKTABLE_FAIL2;
472473
DROPTABLE PKTABLE;
473474

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

0 commit comments

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