Re: Time delayed LR (WAS Re: logical replication restrictions)

On Mon, Feb 27, 2023 at 1:50 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Mon, Feb 27, 2023 at 3:34 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > > ---
> > > Why do we not to delay sending COMMIT PREPARED messages?
> > >
> >
> > I think we need to either add delay for prepare or commit prepared as
> > otherwise, it will lead to delaying the xact more than required.
>
> Agreed.
>
> > The
> > patch seems to add a delay before sending a PREPARE as that is the
> > time when the subscriber will apply the changes.
>
> Considering the purpose of this feature mentioned in the commit
> message "particularly to fix errors that might cause data loss",
> delaying sending PREPARE would really help that situation? For
> example, even after (mistakenly) executing PREPARE for a transaction
> executing DELETE without WHERE clause on the publisher the user still
> can rollback the transaction. They don't lose data on both nodes yet.
> After executing (and replicating) COMMIT PREPARED for that
> transaction, they lose the data on both nodes. IIUC the time-delayed
> logical replication should help this situation by delaying sending
> COMMIT PREPARED so that, for example, the user can stop logical
> replication before COMMIT PREPARED message arrives to the subscriber.
> So I think we should delay sending COMMIT PREPARED (and COMMIT)
> instead of PREPARE. This would help users to correct data loss errors,
> and would be more consistent with what recovery_min_apply_delay does.
>

The one difference w.r.t recovery_min_apply_delay is that here we will
hold locks for the duration of the delay which didn't seem to be a
good idea. This will also probably lead to more bloat as we will keep
transactions open for a long time. Doing it before DecodePrepare won't
have such problems. This is the reason that we decide to perform a
delay at the start of the transaction instead at commit/prepare in the
subscriber-side approach.

--
With Regards,
Amit Kapila.