Re: Virtual generated columns

On 14.11.24 10:46, Amit Kapila wrote:
>> 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.

Row filter expressions have restrictions that virtual columns do not
have. For example, row filter expressions cannot use user-defined
functions. If you have a virtual column that uses a user-defined
function and then you create a row filter using that virtual column, you
get an error when you create the publication. (This does not work
correctly in the posted patches, but it will in v10 that I will post
shortly.) This behavior is ok, I think, you get the error when you
write the faulty expression, and it's straightforward to implement.

Now let's say that we implement what you suggest that we compute virtual
columns during logical decoding. Then we presumably need similar
restrictions, like not allowing user-defined functions.

Firstly, I don't know if that would be such a good restriction. For row
filters, that's maybe ok, but for virtual columns, you want to be able
to write complex and interesting expressions, otherwise you wouldn't
need a virtual column.

And secondly, we'd then need to implement logic to check that you can't
add a table with a virtual column with a user-defined function to a
publication. This would happen not when you write the expression but
only later when you operate on the table or publication. So it's
already a dubious user experience.

And the number of combinations and scenarios that you'd need to check
there is immense. (Not just CREATE PUBLICATION and ALTER PUBLICATION,
but also CREATE TABLE when a FOR ALL TABLES publication exists, ALTER
TABLE when new columns are added, new partitions are attached, and so
on.) Maybe someone wants to work on that, but that's more than I am
currently signed up for. And given the first point, I'm not sure if
it's even such a useful feature.

I think, for the first iteration of this virtual generated columns
feature, the publish_generated_columns option should just not apply to
it. Whether that means renaming the option or just documenting this is
something for discussion.