Re: Transactions involving multiple postgres foreign servers, take 2

> 2021/06/04 17:16、Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>のメール:
>
> On Fri, Jun 4, 2021 at 3:58 PM ikedamsh(at)oss(dot)nttdata(dot)com
> <ikedamsh(at)oss(dot)nttdata(dot)com> wrote:
>>
>>
>>
>> 2021/06/04 12:28、Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>のメール:
>>
>>
>> Thank you for pointing it out. This idea has been proposed several
>> times and there were discussions. I'd like to summarize the proposed
>> ideas and those pros and cons before replying to your other comments.
>>
>> There are 3 ideas. After backend both prepares all foreign transaction
>> and commit the local transaction,
>>
>> 1. the backend continues attempting to commit all prepared foreign
>> transactions until all of them are committed.
>> 2. the backend attempts to commit all prepared foreign transactions
>> once. If an error happens, leave them for the resolver.
>> 3. the backend asks the resolver that launched per foreign server to
>> commit the prepared foreign transactions (and backend waits or doesn't
>> wait for the commit completion depending on the setting).
>>
>> With ideas 1 and 2, since the backend itself commits all foreign
>> transactions the resolver process cannot be a bottleneck, and probably
>> the code can get more simple as backends don't need to communicate
>> with resolver processes.
>>
>> However, those have two problems we need to deal with:
>>
>>
>> Thanks for sharing the summarize. I understood there are problems related to
>> FDW implementation.
>>
>> First, users could get an error if an error happens during the backend
>> committing prepared foreign transaction but the local transaction is
>> already committed and some foreign transactions could also be
>> committed, confusing users. There were two opinions to this problem:
>> FDW developers should be responsible for writing FDW code such that
>> any error doesn't happen during committing foreign transactions, and
>> users can accept that confusion since an error could happen after
>> writing the commit WAL even today without this 2PC feature. For the
>> former point, I'm not sure it's always doable since even palloc()
>> could raise an error and it seems hard to require all FDW developers
>> to understand all possible paths of raising an error. And for the
>> latter point, that's true but I think those cases are
>> should-not-happen cases (i.g., rare cases) whereas the likelihood of
>> an error during committing prepared transactions is not low (e.g., by
>> network connectivity problem). I think we need to assume that that is
>> not a rare case.
>>
>>
>> Hmm… Sorry, I don’t have any good ideas now.
>>
>> If anything, I’m on second side which users accept the confusion though
>> let users know a error happens before local commit is done or not is necessary
>> because if the former case, users will execute the same query again.
>
> Yeah, users will need to remember the XID of the last executed
> transaction and check if it has been committed by pg_xact_status().
>
>>
>>
>> The second problem is whether we can cancel committing foreign
>> transactions by pg_cancel_backend() (or pressing Ctl-c). If the
>> backend process commits prepared foreign transactions, it's FDW
>> developers' responsibility to write code that is interruptible. I’m
>> not sure it’s feasible for drivers for other databases.
>>
>>
>> Sorry, my understanding is not clear.
>>
>> After all prepares are done, the foreign transactions will be committed.
>> So, does this mean that FDW must leave the unresolved transaction to the transaction
>> resolver and show some messages like “Since the transaction is already committed,
>> the transaction will be resolved in background" ?
>
> I think this would happen after the backend cancels COMMIT PREPARED.
> To be able to cancel an in-progress query the backend needs to accept
> the interruption and send the cancel request. postgres_fdw can do that
> since libpq supports sending a query and waiting for the result but
> I’m not sure about other drivers.

Thanks, I understood that handling this issue is not scope of the 2PC feature
as Tsunakawa-san and you said,

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION