Re: Virtual generated columns

On Tue, Nov 12, 2024 at 9:47 PM Peter Eisentraut <peter(at)eisentraut(dot)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.