Horton

Re: Virtual generated columns

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, jian he <jian.universality@gmail.com>, Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2024-11-14T09:46:05Z
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 Tue, Nov 12, 2024 at 9:47 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 10.11.24 04:16, Amit Kapila wrote:
> > The possible idea to replicate virtual generated columns is to compute
> > the corresponding expression before sending the data to the client. If
> > we can allow it in the row filter than why not to publish it as well.
>
> Row filters have pretty strong restrictions for what kind of operations
> they can contain.  Applying those restrictions to virtual generated
> columns would probably not make that feature very useful.  (You want to
> use virtual columns for expressions that are too cumbersome to write out
> by hand every time.)
>

From this paragraph, it sounds like you are saying we can't support
virtual columns in row filters. But the patch already works (not
checked all possible cases). For example,

postgres=# CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED
ALWAYS AS (a * 2) VIRTUAL);
CREATE TABLE
postgres=# create publication pub2 for table gtest1 WHERE (b > 5);
CREATE PUBLICATION

After this, Insert also adheres to this row filter. I haven't tested
it in any further detail but its basic usage in row filters works.

> Moreover, we would have to implement some elaborate cross-checks if a
> table gets added to a publication.  How would that work?  "Can't add
> table x to publication because it contains a virtual generated column
> with a non-simple expression"?  With row filters, this is less of a
> problem, because the row filter a property of the publication.
>

Because virtual generated columns work in row filters, so I thought it
could follow the rules for column lists as well. If the virtual column
doesn't adhere to the rules of the row filter then it shouldn't even
work there. My response was based on the theory that the expression
for virtual columns could be computed during logical decoding. So,
let's first clarify that before discussing this point further.

-- 
With Regards,
Amit Kapila.