On Tue, Nov 4, 2025 at 8:27 AM Masahiko Sawada <[email protected]> wrote:
>
> The proposed patch (the 0002 patch) allows COPY FROM ... WHERE to
> filter rows by checking tuples including generated column values but
> it's somewhat odd as it seems not to be the time of reading tuples
> from a table.
>
> Also, the patch calls ExecComputeStoredGenerated() before ExecQual(),
> which is also before we trigger the BEFORE INSERT trigger. It clearly
> violates what the documentation describes[1]:
>
> For example, the tuples passed to a BEFORE INSERT trigger varies
> depending on the WHERE clause as follows:
>
> -- preparation
> create table t (a int, s int generated always as (a + 10) stored);
> create table tt (a int, s int);
> create function trig_fn() returns trigger as
> $$
> begin
> insert into tt select NEW.*;
> return NEW;
> end;
> $$ language plpgsql;
> create trigger trig before insert on t for each row execute function
> trig_fn();
>
> -- copy a row without the WHERE clause.
> copy t from program 'echo 1';
> table tt;
> a | s
> ---+---
> 1 |
> (1 row)
>
> -- copy a row with the where clause
> copy t from program 'echo 1' where s > 0;
> table tt;
> a | s
> ---+----
> 1 |
> 1 | 11
>
> > 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.
> > 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);
Another possible reason:
tableoid can be referenced in virtual generated column expression.
COPY WHERE clause can be supported for virtual general columns.
CREATE TABLE gtest4 (a int, b oid GENERATED ALWAYS AS ((tableoid)));
COPY gtest4 from stdin where b <> 26420;
COPY gtest4 from stdin where tableoid <> 26420;
we should expect the above two COPY statements behave the same.
please check the attached file:
v2-0001: fix COPY WHERE with system column reference
v2-0002: fix COPY WHERE with virtual generated column reference
v2-0003: fix COPY WHERE with stored generated column reference (experimental)
From 9706a4ebbcfe186b2d6c9fca37187518e4f917c1 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 4 Nov 2025 19:41:56 +0800
Subject: [PATCH v2 3/3] fix COPY WHERE clause stored generated column
references
discussion: https://postgr.es/m/cacjufxhb8ypq095r_pydr77w9xknaxg5rzy-wp525mkq+hr...@mail.gmail.com
---
src/backend/commands/copyfrom.c | 56 ++++++++++++++++++-
.../regress/expected/generated_stored.out | 14 ++++-
src/test/regress/sql/generated_stored.sql | 13 +++++
3 files changed, 81 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 12781963b4f..7f1a4728f93 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -785,6 +785,7 @@ CopyFrom(CopyFromState cstate)
ModifyTableState *mtstate;
ExprContext *econtext;
TupleTableSlot *singleslot = NULL;
+ TupleTableSlot *tmpslot = NULL;
MemoryContext oldcontext = CurrentMemoryContext;
PartitionTupleRouting *proute = NULL;
@@ -798,6 +799,7 @@ CopyFrom(CopyFromState cstate)
int64 excluded = 0;
bool has_before_insert_row_trig;
bool has_instead_insert_row_trig;
+ bool has_generated_stored = false;
bool leafpart_use_multi_insert = false;
Assert(cstate->rel);
@@ -908,6 +910,34 @@ CopyFrom(CopyFromState cstate)
ti_options |= TABLE_INSERT_FROZEN;
}
+ if (cstate->whereClause)
+ {
+ TupleDesc tupDesc = RelationGetDescr(cstate->rel);
+
+ if (tupDesc->constr && tupDesc->constr->has_generated_stored)
+ {
+ int k = -1;
+ Bitmapset *attnums = NULL;
+
+ 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)
+ {
+ has_generated_stored = true;
+ break;
+ }
+ }
+ }
+
+ if (has_generated_stored)
+ tmpslot = table_slot_create(cstate->rel, NULL);
+ }
+
/*
* 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,7 +1218,20 @@ CopyFrom(CopyFromState cstate)
if (cstate->whereClause)
{
- econtext->ecxt_scantuple = myslot;
+ if (has_generated_stored)
+ {
+ ExecClearTuple(tmpslot);
+
+ ExecCopySlot(tmpslot, myslot);
+
+ ExecComputeStoredGenerated(resultRelInfo, estate, tmpslot,
+ CMD_INSERT);
+
+ econtext->ecxt_scantuple = tmpslot;
+ }
+ else
+ econtext->ecxt_scantuple = myslot;
+
/* Skip items that don't match COPY's WHERE clause */
if (!ExecQual(cstate->qualexpr, econtext))
{
@@ -1489,6 +1532,17 @@ CopyFrom(CopyFromState cstate)
ExecResetTupleTable(estate->es_tupleTable, false);
+ if (has_generated_stored)
+ {
+ ExecClearTuple(tmpslot);
+ tmpslot->tts_ops->release(tmpslot);
+ if (tmpslot->tts_tupleDescriptor)
+ {
+ ReleaseTupleDesc(tmpslot->tts_tupleDescriptor);
+ tmpslot->tts_tupleDescriptor = NULL;
+ }
+ }
+
/* Allow the FDW to shut down */
if (target_resultRelInfo->ri_FdwRoutine != NULL &&
target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
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/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)
--
2.34.1
From fe1e51b9028615bb22c75e7d3c4de4fa9c94abf5 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 4 Nov 2025 18:16:07 +0800
Subject: [PATCH v2 2/3] fix COPY WHERE clause virtual generated column
references
discussion: https://postgr.es/m/cacjufxhb8ypq095r_pydr77w9xknaxg5rzy-wp525mkq+hr...@mail.gmail.com
---
src/backend/commands/copy.c | 4 ++++
src/test/regress/expected/generated_virtual.out | 14 +++++++++++++-
src/test/regress/sql/generated_virtual.sql | 13 +++++++++++++
3 files changed, 30 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index a112812d96f..e36d8f4fd07 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 whereClause */
+ whereClause = expand_generated_columns_in_expr(whereClause, rel, 1);
+
pull_varattnos(whereClause, 1, &attnums);
k = -1;
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_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 60f233f24cedb0f70a6cb92488b69ee1fb0edf18 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 4 Nov 2025 18:10:06 +0800
Subject: [PATCH v2 1/3] diallow COPY WHERE clause system column references
discussion: https://postgr.es/m/cacjufxhb8ypq095r_pydr77w9xknaxg5rzy-wp525mkq+hr...@mail.gmail.com
---
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
