Re: repeated decoding of prepared transactions

On Mon, Feb 22, 2021 at 2:57 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> > Yeah, we need to probably store this new point as slot's persistent information.
>
> Should be fine I think...
>

So, we are in agreement that the above solution will work and we won't
need to resend the prepare after the restart. I would like to once
again describe few other points which we are discussing in this and
other thread [1] to see if you or others have any different opinion on
those:

1. With respect to SQL APIs, currently 'two-phase-commit' is a plugin
option so it is possible that the first time when it gets changes
(pg_logical_slot_get_changes) *without* 2PC enabled it will not get
the prepared even though prepare is after consistent snapshot. Now
next time during getting changes (pg_logical_slot_get_changes) if the
2PC option is enabled it will skip prepare because by that time
start_decoding_at has been moved. So the user will only get commit
prepared as shown in the example in the email above [2]. I think it
might be better to allow enable/disable of 2PC only at create_slot
time. Markus, Ajin, and I seem to be in agreement on this point. I
think the same will be true for subscriber-side solution as well.

2. There is a possibility that subscribers miss some prepared xacts.
Let me explain the problem and solution. Currently, when we create a
subscription, we first launch apply-worker and create the main apply
worker slot and then launch table sync workers as required. Now,
assume, the apply worker slot is created and after that, we launch
tablesync worker, which will initiate its slot (sync_slot) creation.
Then, on the publisher-side, the situation is such that there is a
prepared transaction that happens before we reach a consistent
snapshot for sync_slot.

Because the WALSender corresponding to apply worker is already running
so it will be in consistent state, for it, such a prepared xact can be
decoded and it will send the same to the subscriber. On the
subscriber-side, it can skip applying the data-modification operations
because the corresponding rel is still not in a ready state (see
should_apply_changes_for_rel and its callers) simply because the
corresponding table sync worker is not finished yet. But prepare will
occur and it will lead to a prepared transaction on the subscriber.

In this situation, tablesync worker has skipped prepare because the
snapshot was not consistent and then it exited because it is in sync
with the apply worker. And apply worker has skipped because tablesync
was in-progress. Later when Commit prepared will come, the
apply-worker will simply commit the previously prepared transaction
and we will never see the prepared transaction data.

For example, consider below situation:
LSN of Prepare t1 = 490, tablesync skipped because it was prior to a
consistent point
LSN of Commit t2 = 500
LSN of commit t3 = 510
LSN of Commit Prepared t1 = 520.

Tablesync worker initially (via copy) got till xact t3 (LSN = 510).
For the apply worker, we get all the above LSN's as it is started
before tablesync worker and reached a consistent point before it. In
the above example, there is a possibility that we miss applying data
for xact t1 as explained in previous paragraphs.

So, the basic premise is that we can't allow tablesync workers to skip
prepared transactions (which can be processed by apply worker) and
process later commits.

I have one idea to address this. When we get the first begin (for
prepared xact) in the apply-worker, we can check if there are any
relations in "not_ready" state and if so then just wait till all the
relations become in sync with the apply worker. This is to avoid that
any of the tablesync workers might skip prepared xact and we don't
want apply worker to also skip the same.

Now, it is possible that some tablesync worker has copied the data and
moved the sync position ahead of where the current apply worker's
position is. In such a case, we need to process transactions in apply
worker such that we can process commits if any, and write prepared
transactions to file. For prepared transactions, we can take decisions
only once the commit prepared for them has arrived.

The other idea I have thought of for this is to only enable 2PC after
initial sync (when both apply worker and tablesync workers are in
sync) is over but I think that can lead to the problem described in
point 1.

[1] - https://www.postgresql.org/message-id/CAA4eK1L%3DdhuCRvyDvrXX5wZgc7s1hLRD29CKCK6oaHtVCPgiFA%40mail.gmail.com
[2] - https://www.postgresql.org/message-id/CAFPTHDbbth0XVwf%3DWXcmp%3D_2nU5oNaK4CxetUr22qi1UM5v6rw%40mail.gmail.com

--
With Regards,
Amit Kapila.