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.