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.