From ccfe28eb42370c1ce7b62c03fc4f2081ab6ba25d Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 6 Nov 2025 11:52:47 +0100 Subject: [PATCH] Disallow generated columns in COPY WHERE clause Stored generated columns are not yet computed when the filtering happens, so we need to prohibit them to avoid incorrect behavior. Co-authored-by: jian he Reviewed-by: Kirill Reshke Reviewed-by: Masahiko Sawada Discussion: https://www.postgresql.org/message-id/flat/CACJufxHb8YPQ095R_pYDr77W9XKNaXg5Rzy-WP525mkq+hRM3g@mail.gmail.com --- src/backend/commands/copy.c | 37 +++++++++++++++++++++++++ src/test/regress/expected/generated.out | 6 ++++ src/test/regress/sql/generated.sql | 4 +++ 3 files changed, 47 insertions(+) diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index e4dee3f11ff..e56eff02c6c 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -128,6 +128,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, if (stmt->whereClause) { + Bitmapset *expr_attrs = NULL; + int i; + /* add nsitem to query namespace */ addNSItemToQuery(pstate, nsitem, false, true, true); @@ -140,6 +143,40 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, /* 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. + */ + pull_varattnos(whereClause, 1, &expr_attrs); + if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs)) + { + expr_attrs = bms_add_range(expr_attrs, + 1 - FirstLowInvalidHeapAttributeNumber, + RelationGetNumberOfAttributes(rel) - FirstLowInvalidHeapAttributeNumber); + expr_attrs = bms_del_member(expr_attrs, 0 - FirstLowInvalidHeapAttributeNumber); + } + + i = -1; + while ((i = bms_next_member(expr_attrs, i)) >= 0) + { + AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber; + + Assert(attno != 0); + + /* + * Prohibit generated columns in the WHERE clause. Stored + * generated columns are not yet computed when the filtering + * happens. + */ + if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated) + ereport(ERROR, + errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("generated columns are not supported in COPY FROM WHERE conditions"), + errdetail("Column \"%s\" is a generated column.", + get_attname(RelationGetRelid(rel), attno, false))); + } + whereClause = eval_const_expressions(NULL, whereClause); whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false); diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index 5cf99c20213..e1161010bbd 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -415,6 +415,12 @@ COPY gtest1 FROM stdin; COPY gtest1 (a, b) FROM stdin; ERROR: column "b" is a generated column DETAIL: Generated columns cannot be used in COPY. +COPY gtest1 FROM stdin WHERE b <> 10; +ERROR: generated columns are not supported in COPY FROM WHERE conditions +DETAIL: Column "b" is a generated column. +COPY gtest1 FROM stdin WHERE gtest1 IS NULL; +ERROR: generated columns are not supported in COPY FROM WHERE conditions +DETAIL: Column "b" is a generated column. SELECT * FROM gtest1 ORDER BY a; a | b ---+--- diff --git a/src/test/regress/sql/generated.sql b/src/test/regress/sql/generated.sql index 1a0b2410498..0dc4e7af932 100644 --- a/src/test/regress/sql/generated.sql +++ b/src/test/regress/sql/generated.sql @@ -189,6 +189,10 @@ COPY gtest1 FROM stdin; COPY gtest1 (a, b) FROM stdin; +COPY gtest1 FROM stdin WHERE b <> 10; + +COPY gtest1 FROM stdin WHERE gtest1 IS NULL; + SELECT * FROM gtest1 ORDER BY a; TRUNCATE gtest3; -- 2.47.3