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

On Fri, 17 Jan 2020 at 07:58, Euler Taveira <euler@timbira.com.br> wrote:
>
> Em qui., 16 de jan. de 2020 às 18:57, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> escreveu:
> >
> > Euler, this patch is still in "waiting on author" since 11/25. Do you
> > plan to review changes made by Amit in the patches he submitted, or what
> > are your plans with this patch?
> >
> Yes, I'm working on Amit suggestions. I'll post a new patch as soon as possible.

Great. I think this'd be nice to see.

Were you able to fully address the following points that came up in
the discussion?

* Make sure row filters cannot access non-catalog, non-user-catalog
relations i.e. can only use RelationIsAccessibleInLogicalDecoding rels

* Prevent filters from attempting to access attributes that may not be
WAL-logged in a given change record, or give them a way to test for
this. Unchanged TOASTed atts are not logged. There's also REPLICA
IDENTITY FULL to consider if exposing access to the old tuple in the
filter.

Also, while I'm not sure if it was raised earlier, experience with row
filtering in pglogical has shown that error handling is challenging.
Because row filters are read from a historic snapshot of the catalogs
you cannot change them or any SQL or plpgsql functions they use if a
problem causes an ERROR when executing the filter expression. You can
fix the current snapshot's definition but the decoding session won't
see it and will continue to ERROR. We don't really have a good answer
for that yet in pglogical; right now you have to either intervene with
low level tools or drop the subscription and re-create it. Neither of
which is ideal.

You can't just read the row filter from the current snapshot as the
relation definition (atts etc) may not match. Plus that creates a
variety of issues with which txns get which version of a row filter
applied during decoding, consistency between multiple subscribers,
etc.

One option I've thought about was a GUC that allows users to specify
what should be done for errors in row filter expressions: drop the row
as if the filter rejected it; pass the row as if the filter matched;
propagate the ERROR and end the decoding session (default).

I'd welcome ideas about this one. I don't think it's a showstopper for
accepting the feature either, we just have to document that great care
is required with any operator or function that could raise an error in
a row filter. But there are just so many often non-obvious ways you
can land up with an ERROR being thrown that I think it's a bit of a
user foot-gun.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise