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

On Friday, December 2, 2022 4:05 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Tue, Nov 15, 2022 at 12:33 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)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?
Hi,

Thank you for your comments !
Below are some analysis for the major points above.

(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.

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.

When we receive stream-in-progress transactions, we'll check whether the time for delay
has passed or not at first in this approach.

(2) About the timeout issue

When having a look at the physical replication internals,
it conducts sending feedback and application of delay separately on different processes.
OTOH, the logical replication needs to achieve those within one process.

When we want to apply delay and avoid the timeout,
we should not store all the transactions data into memory.
So, one approach for this is to serialize the transaction data and after the delay,
we apply the transactions data. However, this means if users adopt this feature,
then all transaction data that should be delayed would be serialized.
We are not sure if this sounds a valid approach or not.

One another approach was to divide the time of delay in apply_delay() and
utilize the divided time for WaitLatch and sends the keepalive messages from there.
But, this approach requires some change on the level of libpq layer
(like implementing a new function for wal receiver in order to monitor if
the data from the publisher is readable or not there).

Probably, the first idea to serialize the delayed transactions might be better on this point.

Any feedback is welcome.

Best Regards,
Takamichi Osumi