Re: Virtual generated columns

On Fri, Nov 29, 2024 at 3:16 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> 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.
>

Fair enough but the same argument applies to the column list. I mean
to say based on the same theory, users will get the ERROR when an
unsupported virtual column type will be used in column the list.

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

Ok. But as mentioned above, we should consider it for the column list.

>
Whether that means renaming the option or just documenting this is
> something for discussion.
>

We can go either way. Say, if we just document it and in the future,
if we want to support it for virtual columns then we need to introduce
another boolean option like publish_generated_virtual_columns. The
other possibility is that we change publish_generated_columns to enum
or string and allow values 's' (stored), 'v' (virtual), and 'n'
(none). Now, only 's' and 'n' will be supported. In the future, if one
wishes to add support for virtual columns, we have a provision to
extend the existing option.

--
With Regards,
Amit Kapila.