On Tue, Nov 4, 2025 at 8:27 AM Masahiko Sawada <sawada.mshk@gmail.com> 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)
