Re: Skipping logical replication transactions on subscriber side

On Wed, Oct 6, 2021 at 11:18 AM Greg Nancarrow <gregn4422(at)gmail(dot)com> wrote:
>
> On Mon, Oct 4, 2021 at 4:31 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > I think here the main point is that does this addresses Peter's
> > concern for this Patch to use a separate syntax? Peter E., can you
> > please confirm? Do let us know if you have something else going in
> > your mind?
> >
>
> Peter's concern seemed to be that the use of a subscription option,
> though convenient, isn't an intuitive natural fit for providing this
> feature (i.e. ability to skip a transaction by xid). I tend to have
> that feeling about using a subscription option for this feature. I'm
> not sure what possible alternative syntax he had in mind and currently
> can't really think of a good one myself that fits the purpose.
>
> 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. 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.

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?

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/