Re: Transactions involving multiple postgres foreign servers, take 2

On Fri, Jun 4, 2021 at 5:16 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> 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().

As the second idea, can we send something like a hint along with the
error (or send a new type of error) that indicates the error happened
after the transaction commit so that the client can decide whether or
not to ignore the error? That way, we can deal with the confusion led
by an error raised after the local commit by the existing post-commit
cleanup routines (and post-commit xact callbacks) as well as by FDW’s
commit prepared routine.

Regards,

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