Re: COPY WHERE clause generated/system column reference

On Mon, 27 Oct 2025 at 13:21, jian he <jian.universality@gmail.com> wrote:
>
> hi.
>
> CREATE TABLE gtest0 (a int, b int GENERATED ALWAYS AS (a + 1) VIRTUAL);
> copy gtest0 from stdin where (b <> 1);
> 0
> \.
>
> ERROR:  unexpected virtual generated column reference
> CONTEXT:  COPY gtest0, line 1: "0"
>
> We need to apply expand_generated_columns_in_expr to the whereClause in DoCopy.
> However, handling STORED generated columns appears to be less straightforward.
>
> currently:
> ExecQual(cstate->qualexpr, econtext))
> happen before
> ExecComputeStoredGenerated.
>
> when calling ExecQual, the stored generated column values have not been
> populated yet. so we may need ExecComputeStoredGenerated beforehand.
> Since ExecComputeStoredGenerated is likely expensive, I added logic to detect
> whether the WHERE clause actually have stored generated columns reference before
> calling it.
>
> generated column allow tableoid system column reference, COPY WHERE clause also
> allow tableoid column reference, should be fine.
>
> please check the attached file:
> v1-0001 fix COPY WHERE with system column reference
> v1-0002 fix COPY WHERE with generated column reference

Hi! Indeed, copying from with generated column in where clause is
broken on HEAD.

I applied your patches, they indeed fix the issue.

Small comment: in 0002:

> + if (has_stored_generated)
> + ExecComputeStoredGenerated(resultRelInfo, estate, myslot,
> +   CMD_INSERT);

Should we use CMD_UTILITY here? Comment in nodes.h suggests so. Also,
ExecComputeStoredGenerated only check for equality with CMD_UPDATE, so
this is just a cosmetic change.



-- 
Best regards,
Kirill Reshke