Re: row filtering for logical replication - Mailing list pgsql-hackers

On Mon, Sep 27, 2021 at 2:02 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Sat, Sep 25, 2021 at 3:36 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
> >
> > On 9/25/21 6:23 AM, Amit Kapila wrote:
> > > On Sat, Sep 25, 2021 at 3:30 AM Tomas Vondra
> > > <tomas.vondra@enterprisedb.com> wrote:
> > >>
> > >> On 9/24/21 7:20 AM, Amit Kapila wrote:
> > >>>
> > >>> I think the right way to support functions is by the explicit marking
> > >>> of functions and in one of the emails above Jeff Davis also agreed
> > >>> with the same. I think we should probably introduce a new marking for
> > >>> this. I feel this is important because without this it won't be safe
> > >>> to access even some of the built-in functions that can access/update
> > >>> database (non-immutable functions) due to logical decoding environment
> > >>> restrictions.
> > >>>
> > >>
> > >> I agree that seems reasonable. Is there any reason why not to just use
> > >> IMMUTABLE for this purpose? Seems like a good match to me.
> > >>
> > >
> > > It will just solve one part of the puzzle (related to database access)
> > > but it is better to avoid the risk of broken replication by explicit
> > > marking especially for UDFs or other user-defined objects. You seem to
> > > be okay documenting such risk but I am not sure we have an agreement
> > > on that especially because that was one of the key points of
> > > discussions in this thread and various people told that we need to do
> > > something about it. I personally feel we should do something if we
> > > want to allow user-defined functions or operators because as reported
> > > in the thread this problem has been reported multiple times. I think
> > > we can go ahead with IMMUTABLE built-ins for the first version and
> > > then allow UDFs later or let's try to find a way for explicit marking.
> > >
> >
> > Well, I know multiple people mentioned that issue. And I certainly agree
> > just documenting the risk would not be an ideal solution. Requiring the
> > functions to be labeled helps, but we've seen people marking volatile
> > functions as immutable in order to allow indexing, so we'll have to
> > document the risks anyway.
> >
> > All I'm saying is that allowing built-in functions/operators but not
> > user-defined variants seems like an annoying break of extensibility.
> > People are used that user-defined stuff can be used just like built-in
> > functions and operators.
> >
>
> I agree with you that allowing UDFs in some way would be good for this
> feature. I think once we get the base feature committed then we can
> discuss whether and how to allow UDFs. Do we want to have an
> additional label for it or can we come up with something which allows
> the user to continue replication even if she has dropped the object
> used in the function? It seems like we can limit the scope of base
> patch functionality to allow the use of immutable built-in functions
> in row filter expressions.
>

OK, immutable system functions are now allowed in v34 [1]

------
[1] https://www.postgresql.org/message-id/CAHut%2BPvWk4w%2BNEAqB32YkQa75tSkXi50cq6suV9f3fASn5C9NA%40mail.gmail.com

Kind Regards,
Peter Smith
Fujitsu Australia