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.
