From b015777f7db4fe18b2550eecc5f396174cf25c38 Mon Sep 17 00:00:00 2001 From: jian he 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