RE: Time delayed LR (WAS Re: logical replication restrictions) - Mailing list pgsql-hackers

On Wednesday, December 7, 2022 2:07 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Tue, Dec 6, 2022 at 5:44 PM Takamichi Osumi (Fujitsu)
> <osumi.takamichi@fujitsu.com> wrote:
> >
> > On Friday, December 2, 2022 4:05 PM Amit Kapila <amit.kapila16@gmail.com>
> wrote:
> > > On Tue, Nov 15, 2022 at 12:33 PM Amit Kapila
> > > <amit.kapila16@gmail.com>
> > > wrote:
> > > > One more thing I would like you to consider is the point raised by
> > > > me related to this patch's interaction with the parallel apply
> > > > feature as mentioned in the email [1]. I am not sure the idea
> > > > proposed in that email [1] is a good one because delaying after
> > > > applying commit may not be good as we want to delay the apply of
> > > > the transaction(s) on subscribers by this feature. I feel this needs more
> thought.
> > > >
> > >
> > > I have thought a bit more about this and we have the following
> > > options to choose the delay point from. (a) apply delay just before
> > > committing a transaction. As mentioned in comments in the patch this
> > > can lead to bloat and locks held for a long time. (b) apply delay
> > > before starting to apply changes for a transaction but here the
> > > problem is which time to consider. In some cases, like for streaming
> > > transactions, we don't receive the commit/prepare xact time in the start
> message. (c) use (b) but use the previous transaction's commit time.
> > > (d) apply delay after committing a transaction by using the xact's commit
> time.
> > >
> > > At this stage, among above, I feel any one of (c) or (d) is worth
> > > considering. Now, the difference between (c) and (d) is that if
> > > after commit the next xact's data is already delayed by more than
> > > min_apply_delay time then we don't need to kick the new logic of apply
> delay.
> > >
> > > The other thing to consider whether we need to process any keepalive
> > > messages during the delay because otherwise, walsender may think
> > > that the subscriber is not available and time out. This may not be a
> > > problem for synchronous replication but otherwise, it could be a problem.
> > >
> > > Thoughts?
> > (1) About the timing to apply the delay
> >
> > One approach of (b) would be best. The idea is to delay all types of
> > transaction's application based on the time when one transaction arrives at
> the subscriber node.
> >
> 
> But I think it will unnecessarily add the delay when there is a delay in sending
> the transaction by the publisher (say due to the reason that publisher was busy
> handling other workloads or there was a temporary network communication
> break between publisher and subscriber). This could probably be the reason
> why physical replication (via recovery_min_apply_delay) uses the commit time of
> the sending side.
You are right. The approach (b) adds additional (or unnecessary) delay
due to network communication or machine troubles in streaming-in-progress cases.
We agreed this approach (b) has the disadvantage.


> > One advantage of this approach over (c) and (d) is that this can avoid
> > the case where we might apply a transaction immediately without
> > waiting, if there are two transactions sequentially and the time in between
> exceeds the min_apply_delay time.
> >
> 
> I am not sure if I understand your point. However, I think even if the
> transactions are sequential but if the time between them exceeds (say because
> the publisher was down) min_apply_delay, there is no need to apply additional
> delay.
I'm sorry, my description was not accurate. 

As for the approach (c), kindly imagine two transactions (txn1, txn2) are executed
on the publisher side and the publisher tries to send both of them to the subscriber.
Here, there is no network trouble and the publisher isn't busy for other workloads.
However, the diff of the time between txn1 and txn2 execeeds "min_apply_delay"
(which is set to the subscriber).

In this case, when the txn2 is a stream-in-progress transaction,
we don't apply any delay for txn2 when it arrives on the subscriber.
It's because before txn2 comes to the subscriber, "min_apply_delay"
has already passed on the publisher side.
This means there's a case we don't apply any delay when we choose approach (c).

The approach (d) has also similar disadvantage.
IIUC, in this approach the subscriber applies delay after committing a transaction,
based on the commit/prepare time of publisher side. Kindly, imagine two transactions
are executed on the publisher and the 2nd transaction completes after the subscriber's delay
for the 1st transaction. Again, there is no network troubles and no heavy workloads on the publisher.
If so, the delay for the txn1 already finishes when the 2nd transaction
arrives on the subscriber, then the 2nd transaction will be applied immediately without delay.

Another new discussion point is to utilize (b) and stream commit/stream prepare time
and apply the delay immediately before applying the spool files of the transactions
in the stream-in-progress transaction cases.

Does someone has any opinion on those approaches ?


Lastly, thanks Amit-san and Kuroda-san for giving me
so many offlist feedbacks about those significant points.



Best Regards,
    Takamichi Osumi