Re: Virtual generated columns

On 29.04.24 20:54, Corey Huinker wrote:
>       -- generation expression must be immutable
>     -CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision
>     GENERATED ALWAYS AS (random()) STORED);
>     +CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision
>     GENERATED ALWAYS AS (random()) VIRTUAL);
> 
> Does a VIRTUAL generated column have to be immutable? I can see where 
> the STORED one has to be, but consider the following:
> 
>     CREATE TABLE foo (
>     created_at timestamptz DEFAULT CURRENT_TIMESTAMP,
>     row_age interval GENERATED ALWAYS AS CURRENT_TIMESTAMP - created_at
>     );

I have been hesitant about this, but I'm now leaning toward that we 
could allow this.

>       -- can't have generated column that is a child of normal column
>       CREATE TABLE gtest_normal (a int, b int);
>     -CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS
>     (a * 2) STORED) INHERITS (gtest_normal);  -- error
>     +CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS
>     (a * 2) VIRTUAL) INHERITS (gtest_normal);  -- error
> 
> This is the barrier to the partitioning reorganization scheme I 
> described above. Is there any hard rule why a child table couldn't have 
> a generated column matching the parent's regular column? I can see where 
> it might prevent indexing that column on the parent table, but is there 
> some other dealbreaker or is this just a "it doesn't work yet" situation?

We had a quite a difficult time getting the inheritance business of 
stored generated columns working correctly.  I'm sticking to the 
well-trodden path here.  We can possibly expand this if someone wants to 
work out the details.

> One last thing to keep in mind is that there are two special case 
> expressions in the spec:
> 
>     GENERATED ALWAYS AS ROW START
>     GENERATED ALWAYS AS ROW END
> 
> and we'll need to be able to fit those into the catalog. I'll start 
> another thread for that unless you prefer I keep it here.

I think this is a separate feature.