Horton

Re: Virtual generated columns

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2024-04-29T18:54:10Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Expand virtual generated columns for ALTER COLUMN TYPE

  2. Eliminate code duplication in replace_rte_variables callbacks

  3. Expand virtual generated columns in the planner

  4. Virtual generated columns

  5. Additional tests for stored generated columns

  6. Improve generated_stored test

  7. Fix handling of CREATE DOMAIN with GENERATED constraint syntax

  8. Add pg_constraint rows for not-null constraints

  9. Put generated_stored test objects in a schema

  10. Rename regress test generated to generated_stored

  11. Small code simplification

  12. Remove useless code

  13. Remove useless initializations

  14. doc: Clarify that pg_attrdef also stores generation expressions

  15. Clean out column-level pg_init_privs entries when dropping tables.

  16. Re-implement the ereport() macro using __VA_ARGS__.

On Mon, Apr 29, 2024 at 4:24 AM Peter Eisentraut <peter@eisentraut.org>
wrote:

> Here is a patch set to implement virtual generated columns.
>

I'm very excited about this!



> The main feature patch (0005 here) generally works but has a number of
> open corner cases that need to be thought about and/or fixed, many of
> which are marked in the code or the tests.  I'll continue working on
> that.  But I wanted to see if I can get some feedback on the test
> structure, so I don't have to keep changing it around later.
>

I'd be very interested to see virtual generated columns working, as one of
my past customers had a need to reclassify data in a partitioned table, and
the ability to detach a partition, alter the virtual generated columns, and
re-attach would have been great. In case you care, it was basically an
"expired" flag, but the rules for what data "expired" varied by country of
customer and level of service.

+ * Stored generated columns cannot work: They are computed after
+ * BEFORE triggers, but partition routing is done before all
+ * triggers.  Maybe virtual generated columns could be made to
+ * work, but then they would need to be handled as an expression
+ * below.

I'd say you nailed it with the test structure. The stored/virtual
copy/split is the ideal way to approach this, which makes the diff very
easy to understand.

+1 for not handling domain types yet.

 -- 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
);


 -- 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?

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.