hi. CREATE TABLE gtest0 (a int, b int GENERATED ALWAYS AS (a + 1) VIRTUAL); copy gtest0 from stdin where (b <> 1); 0 \.
ERROR: unexpected virtual generated column reference CONTEXT: COPY gtest0, line 1: "0" We need to apply expand_generated_columns_in_expr to the whereClause in DoCopy. However, handling STORED generated columns appears to be less straightforward. currently: ExecQual(cstate->qualexpr, econtext)) happen before ExecComputeStoredGenerated. when calling ExecQual, the stored generated column values have not been populated yet. so we may need ExecComputeStoredGenerated beforehand. Since ExecComputeStoredGenerated is likely expensive, I added logic to detect whether the WHERE clause actually have stored generated columns reference before calling it. generated column allow tableoid system column reference, COPY WHERE clause also allow tableoid column reference, should be fine. please check the attached file: v1-0001 fix COPY WHERE with system column reference v1-0002 fix COPY WHERE with generated column reference
From b015777f7db4fe18b2550eecc5f396174cf25c38 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 27 Oct 2025 16:15:58 +0800 Subject: [PATCH v1 2/2] fix COPY WHERE clause generated column references discussion: https://postgr.es/m/ --- src/backend/commands/copy.c | 4 +++ src/backend/commands/copyfrom.c | 36 +++++++++++++++++++ .../regress/expected/generated_stored.out | 14 +++++++- .../regress/expected/generated_virtual.out | 14 +++++++- src/test/regress/sql/generated_stored.sql | 13 +++++++ src/test/regress/sql/generated_virtual.sql | 13 +++++++ 6 files changed, 92 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index a112812d96f..72f78ec647f 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -33,6 +33,7 @@ #include "parser/parse_collate.h" #include "parser/parse_expr.h" #include "parser/parse_relation.h" +#include "rewrite/rewriteHandler.h" #include "utils/acl.h" #include "utils/builtins.h" #include "utils/lsyscache.h" @@ -148,6 +149,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, /* we have to fix its collations too */ assign_expr_collations(pstate, whereClause); + /* Expand virtual generated columns in the expr */ + whereClause = expand_generated_columns_in_expr(whereClause, rel, 1); + pull_varattnos(whereClause, 1, &attnums); k = -1; diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 12781963b4f..3db698f009c 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -798,6 +798,7 @@ CopyFrom(CopyFromState cstate) int64 excluded = 0; bool has_before_insert_row_trig; bool has_instead_insert_row_trig; + bool has_stored_generated = false; bool leafpart_use_multi_insert = false; Assert(cstate->rel); @@ -908,6 +909,37 @@ CopyFrom(CopyFromState cstate) ti_options |= TABLE_INSERT_FROZEN; } + if (cstate->whereClause) + { + TupleDesc tupDesc; + tupDesc = RelationGetDescr(cstate->rel); + + if (tupDesc->constr && tupDesc->constr->has_generated_stored) + { + Bitmapset *attnums = NULL; + int k = -1; + + pull_varattnos(cstate->whereClause, 1, &attnums); + while ((k = bms_next_member(attnums, k)) >= 0) + { + Form_pg_attribute col; + AttrNumber attnum = k + FirstLowInvalidHeapAttributeNumber; + + col = TupleDescAttr(tupDesc, attnum - 1); + if (col->attgenerated == ATTRIBUTE_GENERATED_STORED) + { + /* + * The COPY WHERE clause have generated column references, + * we need to compute the stored generated column while + * evaluating the COPY WHERE clause later. + */ + has_stored_generated = true; + break; + } + } + } + } + /* * We need a ResultRelInfo so we can use the regular executor's * index-entry-making machinery. (There used to be a huge amount of code @@ -1188,6 +1220,10 @@ CopyFrom(CopyFromState cstate) if (cstate->whereClause) { + if (has_stored_generated) + ExecComputeStoredGenerated(resultRelInfo, estate, myslot, + CMD_INSERT); + econtext->ecxt_scantuple = myslot; /* Skip items that don't match COPY's WHERE clause */ if (!ExecQual(cstate->qualexpr, econtext)) diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index adac2cedfb2..d91989ae4cb 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -520,6 +520,7 @@ COPY gtest3 (a, b) TO stdout; ERROR: column "b" is a generated column DETAIL: Generated columns cannot be used in COPY. COPY gtest3 FROM stdin; +COPY gtest3 FROM stdin WHERE (b <> 15); COPY gtest3 (a, b) FROM stdin; ERROR: column "b" is a generated column DETAIL: Generated columns cannot be used in COPY. @@ -530,7 +531,8 @@ SELECT * FROM gtest3 ORDER BY a; 2 | 6 3 | 9 4 | 12 -(4 rows) + 6 | 18 +(5 rows) -- null values CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED); @@ -1067,6 +1069,16 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; gtest_child3 | 09-13-2016 | 1 | 2 (3 rows) +COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2; +SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + tableoid | f1 | f2 | f3 +--------------+------------+----+---- + gtest_child | 07-15-2016 | 2 | 4 + gtest_child | 07-16-2016 | 4 | 8 + gtest_child2 | 08-15-2016 | 3 | 6 + gtest_child3 | 09-13-2016 | 1 | 2 +(4 rows) + -- we leave these tables around for purposes of testing dump/reload/upgrade -- generated columns in partition key (not allowed) CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index c861bd36c5a..047e0daa68b 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -514,6 +514,7 @@ COPY gtest3 (a, b) TO stdout; ERROR: column "b" is a generated column DETAIL: Generated columns cannot be used in COPY. COPY gtest3 FROM stdin; +COPY gtest3 FROM stdin WHERE (b <> 15); COPY gtest3 (a, b) FROM stdin; ERROR: column "b" is a generated column DETAIL: Generated columns cannot be used in COPY. @@ -524,7 +525,8 @@ SELECT * FROM gtest3 ORDER BY a; 2 | 6 3 | 9 4 | 12 -(4 rows) + 6 | 18 +(5 rows) -- null values CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL); @@ -1029,6 +1031,16 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; gtest_child3 | 09-13-2016 | 1 | 2 (3 rows) +COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2; +SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + tableoid | f1 | f2 | f3 +--------------+------------+----+---- + gtest_child | 07-15-2016 | 2 | 4 + gtest_child | 07-16-2016 | 4 | 8 + gtest_child2 | 08-15-2016 | 3 | 6 + gtest_child3 | 09-13-2016 | 1 | 2 +(4 rows) + -- we leave these tables around for purposes of testing dump/reload/upgrade -- generated columns in partition key (not allowed) CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3); diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index f56fde8d4e5..60770a54d89 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -231,6 +231,12 @@ COPY gtest3 FROM stdin; 4 \. +COPY gtest3 FROM stdin WHERE (b <> 15); +5 +6 +\. + + COPY gtest3 (a, b) FROM stdin; SELECT * FROM gtest3 ORDER BY a; @@ -496,6 +502,13 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); \d gtest_child2 \d gtest_child3 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + +COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2; +2016-07-15,1 +2016-07-16,4 +\. +SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + -- we leave these tables around for purposes of testing dump/reload/upgrade -- generated columns in partition key (not allowed) diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index adfe88d74ae..4db335de814 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -231,6 +231,12 @@ COPY gtest3 FROM stdin; 4 \. +COPY gtest3 FROM stdin WHERE (b <> 15); +5 +6 +\. + + COPY gtest3 (a, b) FROM stdin; SELECT * FROM gtest3 ORDER BY a; @@ -539,6 +545,13 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); \d gtest_child2 \d gtest_child3 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + +COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2; +2016-07-15,1 +2016-07-16,4 +\. +SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; + -- we leave these tables around for purposes of testing dump/reload/upgrade -- generated columns in partition key (not allowed) -- 2.34.1
From 8d6b714264a26888da6e94e7e71a596e24fe6597 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 27 Oct 2025 14:30:24 +0800 Subject: [PATCH v1 1/2] diallow COPY WHERE clause system column references discussion: https://postgr.es/m/ --- src/backend/commands/copy.c | 17 +++++++++++++++++ src/test/regress/expected/copy2.out | 3 +++ src/test/regress/sql/copy2.sql | 4 ++++ 3 files changed, 24 insertions(+) diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 44020d0ae80..a112812d96f 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -133,6 +133,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, if (stmt->whereClause) { + Bitmapset *attnums = NULL; + int k; + /* add nsitem to query namespace */ addNSItemToQuery(pstate, nsitem, false, true, true); @@ -145,6 +148,20 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, /* we have to fix its collations too */ assign_expr_collations(pstate, whereClause); + pull_varattnos(whereClause, 1, &attnums); + + k = -1; + while ((k = bms_next_member(attnums, k)) >= 0) + { + AttrNumber attnum = k + FirstLowInvalidHeapAttributeNumber; + + /* Disallow expressions referencing system attributes. */ + if (attnum <= 0 && attnum != TableOidAttributeNumber) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("COPY FROM WHERE on system columns is not supported")); + } + whereClause = eval_const_expressions(NULL, whereClause); whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false); diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index f3fdce23459..cae2c89b95e 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -159,6 +159,9 @@ CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80" COPY x (b, c, d, e) from stdin delimiter ',' null 'x'; COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; +COPY x from stdin WHERE xmin IS NULL; --error +ERROR: COPY FROM WHERE on system columns is not supported +COPY x from stdin WHERE tableoid IS NULL; --ok COPY x TO stdout WHERE a = 1; ERROR: WHERE clause not allowed with COPY TO LINE 1: COPY x TO stdout WHERE a = 1; diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index cef45868db5..383c8204094 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -136,6 +136,10 @@ COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii'; 4008:8:Delimiter:\::\: \. +COPY x from stdin WHERE xmin IS NULL; --error +COPY x from stdin WHERE tableoid IS NULL; --ok +\. + COPY x TO stdout WHERE a = 1; COPY x from stdin WHERE a = 50004; 50003 24 34 44 54 -- 2.34.1
