v1-0002-fix-COPY-WHERE-clause-generated-column-references.patch

From b015777f7db4fe18b2550eecc5f396174cf25c38 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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