Horton

Re: Virtual generated columns

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, jian he <jian.universality@gmail.com>, Zhang Mingli <zmlpostgres@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-02-19T01:42:38Z
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 Tue, 18 Feb 2025 at 13:12, Richard Guo <guofenglinux@gmail.com> wrote:
>
> > It seems to me that, for a relation in the rangetable that has virtual
> > generated columns, we can consider it a subquery to some extent.  For
> > instance, suppose we have a query:
> >
> > select ... from ... join t on ...;
> >
> > and suppose t.b is a virtual generated column.  We can consider this
> > query as:
> >
> > select ... from ... join (select a, expr() as b from t) as t on ...;
> >
> > In this sense, I'm wondering if we can leverage the
> > pullup_replace_vars architecture to expand the virtual generated
> > columns.  I believe this would help avoid a lot of duplicate code with
> > pullup_replace_vars_callback.

Yes, I think this is much better. Having just one place that does that
complex logic is a definite win.

At one point I had the idea of making the rewriter turn RTEs with
virtual generated columns into subquery RTEs, so then the planner
would treat them just like views, but that would have been less
efficient. Also, I think there would have been a problem with RLS
quals, which would have been added to the subquery RTEs. Perhaps that
could have been fixed up during subquery pullup, but it felt ugly and
I didn't actually try it.


> I had a try with this idea, and attached is what I came up with.  It
> fixes all the mentioned issues but still requires significant
> refinement, particularly due to the lack of comments.  By leveraging
> the pullup_replace_vars architecture to expand the virtual generated
> columns, it saves a lot of duplicate code.

One thing I don't like about this is that it's introducing more code
duplication between pullup_replace_vars() and
ReplaceVarsFromTargetList(). Those already had a lot of code in common
before RETURNING OLD/NEW was added, and this is duplicating even more
code. I think it'd be better to refactor so that they share common
code, since it has become quite complex, and it would be better to
have just one place to maintain. Attached is an updated patch doing
that.

Regards,
Dean