Re: Virtual generated columns

On Thu, Jan 9, 2025 at 3:28 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 08.01.25 17:38, Tom Lane wrote:
> > Peter Eisentraut <peter(at)eisentraut(dot)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.""