Skip to content

Commit ba3d39c

Browse files
robertmhaasrobertmhaas
committed
Don't allow system columns in CHECK constraints, except tableoid.
Previously, arbitray system columns could be mentioned in table constraints, but they were not correctly checked at runtime, because the values weren't actually set correctly in the tuple. Since it seems easy enough to initialize the table OID properly, do that, and continue allowing that column, but disallow the rest unless and until someone figures out a way to make them work properly. No back-patch, because this doesn't seem important enough to take the risk of destabilizing the back branches. In fact, this will pose a dump-and-reload hazard for those upgrading from previous versions: constraints that were accepted before but were not correctly enforced will now either be enforced correctly or not accepted at all. Either could result in restore failures, but in practice I think very few users will notice the difference, since the use case is pretty marginal anyway and few users will be relying on features that have not historically worked. Amit Kapila, reviewed by Rushabh Lathia, with doc changes by me.
1 parent ff2a1f5 commit ba3d39c

File tree

7 files changed

+82
-1
lines changed

7 files changed

+82
-1
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -430,7 +430,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
430430
<para>
431431
Currently, <literal>CHECK</literal> expressions cannot contain
432432
subqueries nor refer to variables other than columns of the
433-
current row.
433+
current row. The system column <literal>tableoid</literal>
434+
may be referenced, but not any other system column.
434435
</para>
435436

436437
<para>

‎src/backend/commands/copy.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2217,6 +2217,12 @@ CopyFrom(CopyState cstate)
22172217
if (loaded_oid != InvalidOid)
22182218
HeapTupleSetOid(tuple, loaded_oid);
22192219

2220+
/*
2221+
* Constraints might reference the tableoid column, so initialize
2222+
* t_tableOid before evaluating them.
2223+
*/
2224+
tuple->t_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
2225+
22202226
/* Triggers and stuff need to be invoked in query context. */
22212227
MemoryContextSwitchTo(oldcontext);
22222228

‎src/backend/commands/tablecmds.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3857,6 +3857,12 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
38573857
/* Preserve OID, if any */
38583858
if (newTupDesc->tdhasoid)
38593859
HeapTupleSetOid(tuple, tupOid);
3860+
3861+
/*
3862+
* Constraints might reference the tableoid column, so initialize
3863+
* t_tableOid before evaluating them.
3864+
*/
3865+
tuple->t_tableOid = RelationGetRelid(oldrel);
38603866
}
38613867

38623868
/* Now check any constraints on the possibly-changed tuple */

‎src/backend/executor/nodeModifyTable.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -246,6 +246,12 @@ ExecInsert(TupleTableSlot *slot,
246246
}
247247
else
248248
{
249+
/*
250+
* Constraints might reference the tableoid column, so initialize
251+
* t_tableOid before evaluating them.
252+
*/
253+
tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
254+
249255
/*
250256
* Check the constraints of the tuple
251257
*/
@@ -653,6 +659,12 @@ ExecUpdate(ItemPointer tupleid,
653659
{
654660
LockTupleMode lockmode;
655661

662+
/*
663+
* Constraints might reference the tableoid column, so initialize
664+
* t_tableOid before evaluating them.
665+
*/
666+
tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
667+
656668
/*
657669
* Check the constraints of the tuple
658670
*

‎src/backend/parser/parse_relation.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -551,6 +551,16 @@ scanRTEForColumn(ParseState *pstate, RangeTblEntry *rte, char *colname,
551551
{
552552
/* quick check to see if name could be a system column */
553553
attnum = specialAttNum(colname);
554+
555+
/* In constraint check, no system column is allowed except tableOid */
556+
if (pstate->p_expr_kind == EXPR_KIND_CHECK_CONSTRAINT &&
557+
attnum < InvalidAttrNumber && attnum != TableOidAttributeNumber)
558+
ereport(ERROR,
559+
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
560+
errmsg("system column \"%s\" reference in check constraint is invalid",
561+
colname),
562+
parser_errposition(pstate, location)));
563+
554564
if (attnum != InvalidAttrNumber)
555565
{
556566
/* now check to see if column actually is defined */

‎src/test/regress/input/constraints.source

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,28 @@ INSERT INTO INSERT_TBL VALUES (null, null, null);
126126

127127
SELECT '' AS nine, * FROM INSERT_TBL;
128128

129+
--
130+
-- Check constraints on system columns
131+
--
132+
133+
CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
134+
altitude int,
135+
CHECK (NOT (is_capital AND tableoid::regclass::text = 'sys_col_check_tbl')));
136+
137+
INSERT INTO SYS_COL_CHECK_TBL VALUES ('Seattle', 'Washington', false, 100);
138+
INSERT INTO SYS_COL_CHECK_TBL VALUES ('Olympia', 'Washington', true, 100);
139+
140+
SELECT *, tableoid::regclass::text FROM SYS_COL_CHECK_TBL;
141+
142+
DROP TABLE SYS_COL_CHECK_TBL;
143+
144+
--
145+
-- Check constraints on system columns other then TableOid should return error
146+
--
147+
CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
148+
altitude int,
149+
CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
150+
129151
--
130152
-- Check inheritance of defaults and constraints
131153
--

‎src/test/regress/output/constraints.source

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -204,6 +204,30 @@ SELECT '' AS nine, * FROM INSERT_TBL;
204204
| | |
205205
(7 rows)
206206

207+
--
208+
-- Check constraints on system columns
209+
--
210+
CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
211+
altitude int,
212+
CHECK (NOT (is_capital AND tableoid::regclass::text = 'sys_col_check_tbl')));
213+
INSERT INTO SYS_COL_CHECK_TBL VALUES ('Seattle', 'Washington', false, 100);
214+
INSERT INTO SYS_COL_CHECK_TBL VALUES ('Olympia', 'Washington', true, 100);
215+
ERROR: new row for relation "sys_col_check_tbl" violates check constraint "sys_col_check_tbl_check"
216+
DETAIL: Failing row contains (Olympia, Washington, t, 100).
217+
SELECT *, tableoid::regclass::text FROM SYS_COL_CHECK_TBL;
218+
city | state | is_capital | altitude | tableoid
219+
---------+------------+------------+----------+-------------------
220+
Seattle | Washington | f | 100 | sys_col_check_tbl
221+
(1 row)
222+
223+
DROP TABLE SYS_COL_CHECK_TBL;
224+
--
225+
-- Check constraints on system columns other then TableOid should return error
226+
--
227+
CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
228+
altitude int,
229+
CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
230+
ERROR: system column "ctid" reference in check constraint is invalid
207231
--
208232
-- Check inheritance of defaults and constraints
209233
--

0 commit comments

Comments
 (0)