Horton

Re: Virtual generated columns

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgresql.org>, Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2025-01-09T12:41:09Z
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 Thu, Jan 9, 2025 at 3:28 AM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 08.01.25 17:38, Tom Lane wrote:
> > Peter Eisentraut <peter@eisentraut.org> writes:
> >> On 03.12.24 15:15, jian he wrote:
> >>> SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE
> >>> attgenerated IN ('v') and (attnotnull or not atthasdef);
> >
> >> I don't understand what the purpose of testing attnotnull is.  That is
> >> independent of attgenerated, I think.
> >
> > Does it make any sense to set NOT NULL on a generated column (virtual
> > or otherwise, but especially virtual)?  What is the system supposed
> > to do if the expression evaluates to null?  That concern generalizes
> > to any constraint really.  Even if we checked it at row storage time,
> > there's no real guarantee that the expression is immutable enough
> > to pass the constraint later.
>
> The generation expression is required to be immutable.  So a table
> definition like
>
>     a int,
>     b int generated always as (a * 2) virtual,
>     check (b > 0)
>
> is not very different from
>
>     a int,
>     check (a * 2 > 0)
>
> in terms of the constraint execution.
>
> The current patch does not support not-null constraints, but that's
> mostly because it's not implemented yet.  Maybe that's what Jian was
> thinking about.
>
yes.
we have 4 four appearance of
errmsg("not-null constraints are not supported on virtual generated columns")
which means there are many ways to not-null constraint to virtual
generated columns.
But in the current patch, the virtual generated column cannot be not-null,
that's why i add attnotnull check.


we can not ALTER COLUMN DROP EXPRESSION for virtual for now.
so the following comments in generated_virtual.sql conflict with the output.
```
-- check that dependencies between columns have also been removed
ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
\d gtest29
```
we can also comment out line 557 in generated_virtual.sql.

attach patch is removing unnecessary parentheses from
```ereport(ERROR, (errcode```
only for this patch related.
per commit https://git.postgresql.org/cgit/postgresql.git/commit/?id=e3a87b4991cc2d00b7a3082abb54c5f12baedfd1

also https://www.postgresql.org/docs/current/error-message-reporting.html says
 ""The extra parentheses were required before PostgreSQL version 12,
but are now optional.""