Re: Virtual generated columns

On 03.12.24 15:15, jian he wrote:
> -- check constraints
> CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a
> * 2) VIRTUAL CHECK (b < 50));
> INSERT INTO gtest20 (a) VALUES (10); -- ok
> INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
>
> ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); --
> violates constraint
> ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
> -----
> The above content is in src/test/regress/sql/generated_virtual.sql,
> the last two query comments
> seem to conflict with the error message for now.

Fixed the comment in the test in patch v11.

> i add some regress tests for your v10 changes in
> src/backend/commands/statscmds.c.
> please check attached.

Added to patch v11.

> the sql tests,
> "sanity check of system catalog" maybe place it to the end of the sql
> file will have better chance of catching some error.
> for virtual, we can also check attnotnull, atthasdef value.
> like:
> SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE
> attgenerated IN ('v') and (attnotnull or not atthasdef);

I moved the existing check to the bottom, as you suggest.

I don't understand what the purpose of testing attnotnull is. That is
independent of attgenerated, I think.