postgres.git / summary / log / commit / refs

commit    8278737bfd0a37b68121a42a0b0a6775488adb48
Author:   Peter Eisentraut <peter@eisentraut.org>
Date:     Thu Nov 06 10:52:47 2025 +0000

    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 <jian.universality@gmail.com>
    Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
    Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
    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 cc1909ecfc4..e7efb811f66 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 021da9cb35a..20e8ada242c 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -435,6 +435,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 271a93a8302..d6236bd174c 100644
--- a/src/test/regress/sql/generated.sql
+++ b/src/test/regress/sql/generated.sql
@@ -204,6 +204,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;


[parent: 1cba25e4cf2e]