Horton

Re: Virtual generated columns

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Zhang Mingli <zmlpostgres@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, Dean Rasheed <dean.a.rasheed@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-02-10T04:52:45Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Expand virtual generated columns for ALTER COLUMN TYPE

  2. Eliminate code duplication in replace_rte_variables callbacks

  3. Expand virtual generated columns in the planner

  4. Virtual generated columns

  5. Additional tests for stored generated columns

  6. Improve generated_stored test

  7. Fix handling of CREATE DOMAIN with GENERATED constraint syntax

  8. Add pg_constraint rows for not-null constraints

  9. Put generated_stored test objects in a schema

  10. Rename regress test generated to generated_stored

  11. Small code simplification

  12. Remove useless code

  13. Remove useless initializations

  14. doc: Clarify that pg_attrdef also stores generation expressions

  15. Clean out column-level pg_init_privs entries when dropping tables.

  16. Re-implement the ereport() macro using __VA_ARGS__.

Attachments

On Mon, Feb 10, 2025 at 11:54 AM Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Sun, Feb 9, 2025 at 7:02 PM Zhang Mingli <zmlpostgres@gmail.com> wrote:
> > On Feb 9, 2025 at 16:00 +0800, Alexander Lakhin <exclusion@gmail.com>, wrote:
> > Please look at a planner error with a virtual generated column triggered
> > by the following script:
> > CREATE TABLE t(a int, b int GENERATED ALWAYS AS (a * 1));
> >
> > SELECT SUM(CASE WHEN t.b = 1 THEN 1 ELSE 1 END) OVER (PARTITION BY t.a)
> > FROM t AS t1 LEFT JOIN T ON true;
> >
> > ERROR:  XX000: wrong varnullingrels (b) (expected (b 3)) for Var 2/1
> > LOCATION:  search_indexed_tlist_for_var, setrefs.c:2901
>
> > During the parse stage, we set the Var->varnullingrels in the parse_analyze_fixedparams function.
> > Later, when rewriting the parse tree in pg_rewrite_query() to expand virtual columns, we replace the expression column b with a new Var that includes a, since b is defined as a * 1.
> > Unfortunately, we overlooked updating the Var->varnullingrels at this point.
> > As a result, when we enter search_indexed_tlist_for_var, it leads to a failure.
> > While we do have another target entry with the correct varnullingrels, the expression involving the virtual column generates another column reference, which causes the error.
> > Currently, I don't have a solid fix.
> > One potential solution is to correct the Vars at or after the rewrite stage by traversing the parse tree again using markNullableIfNeeded.
> > However, this approach may require exposing the ParseState, which doesn't seem ideal.
> > It appears that the virtual column generation function during the rewrite stage does not account for the Var field settings, leading to the errors we are encountering.
>
> Hmm, would it be possible to propagate any varnullingrels into the
> replacement expression in ReplaceVarsFromTargetList_callback()?
>
in ReplaceVarsFromTargetList_callback,
we have
``if (var->varlevelsup > 0)``
``if (var->varreturningtype != VAR_RETURNING_DEFAULT)``

we can also have.
 ``if (var->varnullingrels != NULL)``

please check attached.

> BTW, I was curious about what happens if the replacement expression is
> constant, so I tried running the query below.
>
> CREATE TABLE t (a int, b int GENERATED ALWAYS AS (1 + 1));
> INSERT INTO t VALUES (1);
> INSERT INTO t VALUES (2);
>
> # SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
>  a | b
> ---+---
>    | 2
>    | 2
> (2 rows)
>
> Is this the expected behavior?  I was expecting that t2.b should be
> all NULLs.
>
SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
should be same as
SELECT t2.a,  2 as b FROM t t1 LEFT JOIN t t2 ON FALSE;
so i think this is expected.