Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w934z-00198n-33 for pgsql-hackers@arkaria.postgresql.org; Sat, 04 Apr 2026 15:38:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w934x-00H3Z3-2N for pgsql-hackers@arkaria.postgresql.org; Sat, 04 Apr 2026 15:38:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w934x-00H3Yv-11 for pgsql-hackers@lists.postgresql.org; Sat, 04 Apr 2026 15:38:31 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1w934t-00000000XXS-44h8 for pgsql-hackers@lists.postgresql.org; Sat, 04 Apr 2026 15:38:30 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 634FcOQi4035885; Sat, 4 Apr 2026 11:38:24 -0400 From: Tom Lane To: Alexander Lakhin cc: Robert Haas , PostgreSQL Hackers Subject: Re: TupleDescAttr bounds checks In-reply-to: <3956971.1775313533@sss.pgh.pa.us> References: <1628959.1774023745@sss.pgh.pa.us> <1631625.1774025167@sss.pgh.pa.us> <3429884.1774238081@sss.pgh.pa.us> <6f435023-8ab6-47c2-ba07-035d0c4212f9@gmail.com> <3956199.1775313002@sss.pgh.pa.us> <3956971.1775313533@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Sat, 04 Apr 2026 10:38:53 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <4035825.1775317063.0@sss.pgh.pa.us> Date: Sat, 04 Apr 2026 11:38:24 -0400 Message-ID: <4035884.1775317104@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <4035825.1775317063.1@sss.pgh.pa.us> I wrote: > But I bet this loop should throw an error for system columns, too, > since we surely won't have computed those either. After poking at that: testing tableoid does sort of work, in that it reads as the OID of the target table named in COPY. But I think any rational use for a test on tableoid here would be in connection with a partitioned target table, and the user would wish it to read as the OID of the destination partition. So I think we should disallow tableoid along with the other system columns, pending somebody having the ambition to make that work. So I propose the attached for HEAD. (I couldn't resist the temptation to clean up adjacent comments.) In the back branches it might be better to just ignore system columns here, on the tiny chance that somebody thinks they do something useful. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v1-reject-system-columns-in-COPY-WHERE.patch"; charset="us-ascii" Content-ID: <4035825.1775317063.2@sss.pgh.pa.us> Content-Description: v1-reject-system-columns-in-COPY-WHERE.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index e837f417d0d..003b70852bb 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -137,22 +137,22 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, Bitmapset *expr_attrs =3D NULL; int i; = - /* add nsitem to query namespace */ + /* Add nsitem to query namespace */ addNSItemToQuery(pstate, nsitem, false, true, true); = /* Transform the raw expression tree */ whereClause =3D transformExpr(pstate, stmt->whereClause, EXPR_KIND_COP= Y_WHERE); = - /* Make sure it yields a boolean result. */ + /* Make sure it yields a boolean result */ whereClause =3D coerce_to_boolean(pstate, whereClause, "WHERE"); = - /* we have to fix its collations too */ + /* We have to fix its collations too */ assign_expr_collations(pstate, whereClause); = /* - * Examine all the columns in the WHERE clause expression. When - * the whole-row reference is present, examine all the columns of - * the table. + * Identify all columns used in the WHERE clause's expression. If + * there's a whole-row reference, replace it with a range of all + * the user columns (caution: that'll include dropped columns). */ pull_varattnos(whereClause, 1, &expr_attrs); if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs)) @@ -163,12 +163,30 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, expr_attrs =3D bms_del_member(expr_attrs, 0 - FirstLowInvalidHeapAttr= ibuteNumber); } = + /* Now we can scan each column needed in the WHERE clause */ i =3D -1; while ((i =3D bms_next_member(expr_attrs, i)) >=3D 0) { AttrNumber attno =3D i + FirstLowInvalidHeapAttributeNumber; + Form_pg_attribute att; = - Assert(attno !=3D 0); + Assert(attno !=3D 0); /* removed above */ + + /* + * Prohibit system columns in the WHERE clause. They won't + * have been filled yet when the filtering happens. (We could + * allow tableoid, but right now it isn't really useful: it + * will read as the target table's OID. Any conceivable use + * for such a WHERE clause would probably wish it to read as + * the target partition's OID, which is not known yet. + * Disallow it to keep flexibility to change that sometime.) + */ + if (attno < 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("system columns are not supported in COPY FROM WHERE condit= ions"), + errdetail("Column \"%s\" is a system column.", + get_attname(RelationGetRelid(rel), attno, false))); = /* * Prohibit generated columns in the WHERE clause. Stored @@ -177,7 +195,8 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, * would need to expand them somewhere around here), but for * now we keep them consistent with the stored variant. */ - if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated) + att =3D TupleDescAttr(RelationGetDescr(rel), attno - 1); + if (att->attgenerated && !att->attisdropped) ereport(ERROR, errcode(ERRCODE_INVALID_COLUMN_REFERENCE), errmsg("generated columns are not supported in COPY FROM WHERE con= ditions"), @@ -185,6 +204,7 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, get_attname(RelationGetRelid(rel), attno, false))); } = + /* Reduce WHERE clause to standard list-of-AND-terms form */ whereClause =3D eval_const_expressions(NULL, whereClause); = whereClause =3D (Node *) canonicalize_qual((Expr *) whereClause, false= ); diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expect= ed/copy2.out index 01101c71051..7600e5239d2 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -204,6 +204,9 @@ COPY x from stdin WHERE a =3D row_number() over(b); ERROR: window functions are not allowed in COPY FROM WHERE conditions LINE 1: COPY x from stdin WHERE a =3D row_number() over(b); ^ +COPY x from stdin WHERE tableoid =3D 'x'::regclass; +ERROR: system columns are not supported in COPY FROM WHERE conditions +DETAIL: Column "tableoid" is a system column. -- check results of copy in SELECT * FROM x; a | b | c | d | e = diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.s= ql index 889dcf1383f..e0810109473 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -168,6 +168,8 @@ COPY x from stdin WHERE a IN (generate_series(1,5)); = COPY x from stdin WHERE a =3D row_number() over(b); = +COPY x from stdin WHERE tableoid =3D 'x'::regclass; + = -- check results of copy in SELECT * FROM x; ------- =_aaaaaaaaaa0--