Re: Transactions involving multiple postgres foreign servers, take 2

On Fri, Jun 4, 2021 at 5:04 PM tsunakawa(dot)takay(at)fujitsu(dot)com
<tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
>
> From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> 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:
> >
>
> > 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.
>
> Why does the user have to get an error? Once the local transaction has been prepared, which means all remote ones also have been prepared, the whole transaction is determined to commit. So, the user doesn't have to receive an error as long as the local node is alive.

I think we should neither ignore the error thrown by FDW code nor
lower the error level (e.g., ERROR to WARNING).

>
> > 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.
>
> How do non-2PC and 2PC cases differ in the rarity of the error?

I think the main difference would be that in 2PC case there will be
network communications possibly with multiple servers after the local
commit.

>
>
> > 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.
>
> That's true not only for prepare and commit but also for other queries. Why do we have to treat prepare and commit specially?

Good point. This would not be a blocker for ideas 1 and 2 but is a
side benefit of idea 3.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/