From: | Peter Eisentraut <peter(at)eisentraut(dot)org> |
---|---|
To: | Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Virtual generated columns |
Date: | 2024-05-22 17:25:59 |
Message-ID: | 6638c181-45d5-42b9-bb49-a34497a9414a@eisentraut.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2024-05-22 17:27:56 | Re: Schema variables - new implementation for Postgres 15 |
Previous Message | Tom Lane | 2024-05-22 17:25:46 | Re: Schema variables - new implementation for Postgres 15 |