On 04.11.25 12:43, jian he wrote:
generated column allow tableoid system column reference, COPY WHERE clause also
allow tableoid column reference, should be fine.
for virtual generated column, adding
``whereClause = expand_generated_columns_in_expr(whereClause, rel, 1);``
should be able to solve the problem.
For stored generated columns, we can either
A. document that the stored generated column is not yet computed, it
will be NULL
B. error out if the WHERE clause has a stored generated column.
C. add a temp slot and the computed stored generated column value
stored in the temp slot.
attached v2-0003 using option C to address this problem.
For backpatching, I suggest that we prohibit both stored and virtual
generated column in the COPY WHERE clause. They don't work anyway, so
this doesn't change anything except get a better error message.
We can then consider adding support in future releases, similar to how
we are expanding their use in other contexts in other patches.
Attached is my proposed patch. I kept it similar to the recently
committed fix in commit ba99c9491c4. Note that we also need to consider
whole-row references, as that patch did.
please check the attached file:
v1-0001 fix COPY WHERE with system column reference
It seems to make sense to disallow users to specify system columns in
the WHERE clause of COPY FROM. But why do we need to have an exception
for tableoid? In the context of COPY FROM, specifying tableoid doesn't
not make sense to me as tuples don't come from any relations. If we
accept tableoid, I think it's better to explain why here.
In function CopyFrom, we have below comment, which indicates
At that time, tableoid was considered in the WHERE clause.
/*
* Constraints and where clause might reference the tableoid column,
* so (re-)initialize tts_tableOid before evaluating them.
*/
myslot->tts_tableOid =
RelationGetRelid(target_resultRelInfo->ri_RelationDesc);
I think this doesn't actually work correctly. I started a separate
thread about this:
https://www.postgresql.org/message-id/flat/30c39ee8-bb11-4b8f-9697-45f7e018a8d3%40eisentraut.org
Until that is solved, I think we don't need to do anything about system
columns. System columns other than tableoid are already rejected. Once
we know what, if anything, to do about tableoid, we can implement a more
complete check.
From 60070278cf52b286e6b0275dee05565f24773bc6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Wed, 5 Nov 2025 12:29:28 +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.
Virtual generated columns currently error out ("unexpected virtual
generated column reference"). They could probably work if we expand
them in the right place, but for now let's keep them consistent with
the stored variant. This doesn't change the behavior, it only gives a
nicer error message.
Co-authored-by: jian he <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Reviewed-by: Masahiko Sawada <[email protected]>
Discussion:
https://www.postgresql.org/message-id/flat/cacjufxhb8ypq095r_pydr77w9xknaxg5rzy-wp525mkq+hr...@mail.gmail.com
---
src/backend/commands/copy.c | 39 +++++++++++++++++++
.../regress/expected/generated_stored.out | 6 +++
.../regress/expected/generated_virtual.out | 6 +++
src/test/regress/sql/copy2.sql | 1 -
src/test/regress/sql/generated_stored.sql | 4 ++
src/test/regress/sql/generated_virtual.sql | 4 ++
6 files changed, 59 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 74ae42b19a7..1a25d36e34a 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 *expr_attrs = NULL;
+ int i;
+
/* add nsitem to query namespace */
addNSItemToQuery(pstate, nsitem, false, true, true);
@@ -145,6 +148,42 @@ 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. Virtual generated columns could
probably work (we
+ * would need to expand them somewhere around
here), but for
+ * now we keep them consistent with the stored
variant.
+ */
+ if (TupleDescAttr(RelationGetDescr(rel), attno
- 1)->attgenerated)
+ ereport(ERROR,
+
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ 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_stored.out
b/src/test/regress/expected/generated_stored.out
index b3710a49de6..8b7a71d8f0c 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -502,6 +502,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/expected/generated_virtual.out
b/src/test/regress/expected/generated_virtual.out
index c5a993cbd8d..b7da03ce7ea 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -496,6 +496,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/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..f33e3ca865f 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -158,7 +158,6 @@ CREATE TRIGGER trg_x_before BEFORE INSERT ON x
COPY x from stdin WHERE a = row_number() over(b);
-
-- check results of copy in
SELECT * FROM x;
diff --git a/src/test/regress/sql/generated_stored.sql
b/src/test/regress/sql/generated_stored.sql
index 99ea0105685..2001a47bcc6 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -217,6 +217,10 @@ CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS
(a || '+' || a) STORED)
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;
diff --git a/src/test/regress/sql/generated_virtual.sql
b/src/test/regress/sql/generated_virtual.sql
index 6683538ac38..81a98995d89 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -217,6 +217,10 @@ CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS
(a || '+' || a) VIRTUAL
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.51.0
