Re: Skipping logical replication transactions on subscriber side - Mailing list pgsql-hackers

On Mon, Oct 25, 2021 at 7:14 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Oct 6, 2021 at 11:18 AM Greg Nancarrow <gregn4422@gmail.com> wrote:
> >
> > I think that the 1st and 2nd patch are useful in their own right, but
> > couldn't this feature (i.e. the 3rd patch) be provided instead as an
> > additional Replication Management function (see 9.27.6)?
> > e.g. pg_replication_skip_xid
> >
>
> After some thoughts on the syntax, it's somewhat natural to me if we
> support the skip transaction feature with another syntax like (I
> prefer the former):
>
> ALTER SUBSCRIPTION ... [SET|RESET] SKIP TRANSACTION xxx;
>
> or
>
> ALTER SUBSCRIPTION ... SKIP TRANSACTION xxx; (setting NONE as XID to
> reset the XID to skip)
>
> The primary reason to have another syntax is that ability to skip a
> transaction seems not to be other subscription parameters such as
> slot_name, binary, streaming that are dumped by pg_dump. FWIW IMO the
> ability to disable the subscription on an error would be a
> subscription parameter. The user is likely to want to specify this
> option also at CREATE SUBSCRIPTION and wants it to be dumped by
> pg_dump. So I think we can think of the skip xid option separately
> from this parameter.
>
> Also, I think we can think of the syntax for this ability (skipping a
> transaction) separately from the syntax of the general conflict
> resolution feature. I guess that we might rather need a whole new
> syntax for conflict resolution.
>

I agree that we will need a separate syntax for conflict resolution
but there is some similarity in what I proposed above (On
Error/Conflict [1]) with the existing syntax of Insert ... On
Conflict. I understand that here the context is different and we are
storing this information in the catalog but still there is some syntax
similarity and it will avoid adding new syntax variants.

> In addition, the user will want to
> dump the definitions of confliction resolution by pg_dump in common
> cases, unlike the skip XID.
>
> As Amit pointed out, we might want to allow users to skip changes
> based on something other than XID but the candidates seem only a few
> to me (LSN, time, and something else?). If these are only a few,
> probably we don’t need to worry about syntax bloat.
>

I guess one might want to skip particular operations that cause an
error and that would be possible as we are providing the relevant
information via a view.

> Regarding an additional replication management function proposed by
> Greg, it seems a bit unnatural to me; the subscription is created and
> altered by DDL but why is only skipping the transaction option
> specified by an SQL function?
>

The one advantage I see is that it will be similar to what we already
have via pg_replication_origin_advance() for skipping WAL during
apply. The other thing could be that this feature can lead to problems
if not used carefully so maybe it is better to provide it only by
special functions. Having said that, I still feel we should do it via
Alter Subscription in some way as that will be convenient to use.

[1] - https://www.postgresql.org/message-id/CAA4eK1%2BBOHXC%3D0S2kA7GkErWq3-QKj34oQvwAPfuTHq%3Depf34w%40mail.gmail.com

--
With Regards,
Amit Kapila.