Re: Virtual generated columns - Mailing list pgsql-hackers

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.