Re: Logical decoding without slots: decoding in lockstep with recovery

On Wed, 23 Dec 2020, 18:57 Amit Kapila, <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Wed, Dec 23, 2020 at 12:26 PM Craig Ringer
> <craig(dot)ringer(at)enterprisedb(dot)com> wrote:
> >
> > Hi all
> >
> > I want to share an idea I've looked at a few times where I've run into
> situations where logical slots were inadvertently dropped, or where it
> became necessary to decode changes in the past on a slot.
> >
> > As most of you will know you can't just create a logical slot in the
> past. Even if it was permitted, it'd be unsafe due to catalog_xmin
> retention requirements and missing WAL.
> >
> > But if we can arrange a physical replica to replay the WAL of interest
> and decode each commit as soon as it's replayed by the startup process, we
> know the needed catalog rows must all exist, so it's safe to decode the
> change.
> >
> > So it should be feasible to run logical decoding in standby, even
> without a replication slot, so long as we:
> >
> > * pause startup process after each xl_xact_commit
> > * wake the walsender running logical decoding
> > * decode and process until ReorderBufferCommit for the just-committed
> xact returns
> > * wake the startup process to decode the up to the next commit
> >
>
> How will you deal with subscriber restart? I think you need some way
> to remember confirmed_flush_lsn and restart_lsn and then need to teach
> WAL machinery to restart from some previous point.
>

The simplest option, albeit slow, would be to require the subscriber to
confirm flush before allowing more WAL redo. That's what I'd initially
assumed. This is a bit of a corner case situation after all, it's never
going to be fast with the switching back and forth.

More efficient would be to decode and output to a local spool file and
fsync it. Then separately send that to the subscriber, like has been
discussed in other work on more efficient logical decoding. So the output
plugin output would go to a local spool.

That can be implemented with pg_recvlogical if needed.

> --
> With Regards,
> Amit Kapila.
>