Re: Transactions involving multiple postgres foreign servers, take 2

On Sat, 18 Jul 2020 at 01:45, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>
>
>
> On 2020/07/17 20:04, Masahiko Sawada wrote:
> > On Fri, 17 Jul 2020 at 14:22, tsunakawa(dot)takay(at)fujitsu(dot)com
> > <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> >>
> >> From: Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>
> >> I have briefly checked the only oracle_fdw but in general I think that
> >>> if an existing FDW supports transaction begin, commit, and rollback,
> >>> these can be ported to new FDW transaction APIs easily.
> >>
> >> Does oracle_fdw support begin, commit and rollback?
> >>
> >> And most importantly, do other major DBMSs, including Oracle, provide the API for preparing a transaction? In other words, will the FDWs other than postgres_fdw really be able to take advantage of the new FDW functions to join the 2PC processing? I think we need to confirm that there are concrete examples.
> >
> > I also believe they do. But I'm concerned that some FDW needs to start
> > a transaction differently when using 2PC. For instance, IIUC MySQL
> > also supports 2PC but the transaction needs to be started with "XA
> > START id” when the transaction needs to be prepared. The transaction
> > started with XA START can be closed by XA END followed by XA PREPARE
> > or XA COMMIT ONE PHASE.
>
> This means that FDW should provide also the API for xa_end()?
> Maybe we need to consider again which API we should provide in FDW,
> based on XA specification?

Not sure that we really need the API for xa_end(). It's not necessary
at least in MySQL case. mysql_fdw can execute either XA END and XA
PREPARE when FDW prepare API is called or XA END and XA COMMIT ONE
PHASE when FDW commit API is called with FDWXACT_FLAG_ONEPHASE.

>
>
> > It means that when starts a new transaction
> > the transaction needs to prepare the transaction identifier and to
> > know that 2PC might be used. It’s quite different from PostgreSQL. In
> > PostgreSQL, we can start a transaction by BEGIN and end it by PREPARE
> > TRANSACTION, COMMIT, or ROLLBACK. The transaction identifier is
> > required when PREPARE TRANSACTION.
> >
> > With MySQL, I guess FDW needs a way to tell the (next) transaction
> > needs to be started with XA START so it can be prepared. It could be a
> > custom GUC or an SQL function. Then when starts a new transaction on
> > MySQL server, FDW can generate and store a transaction identifier into
> > somewhere alongside the connection. At the prepare phase, it passes
> > the transaction identifier via GetPrepareId() API to the core.
> >
> > I haven’t tested the above yet and it’s just a desk plan. it's
> > definitely a good idea to try integrating this 2PC feature to FDWs
> > other than postgres_fdw to see if design and interfaces are
> > implemented sophisticatedly.
>
> With the current patch, we track whether write queries are executed
> in each server. Then, if the number of servers that execute write queries
> is less than two, 2PC is skipped. This "optimization" is not necessary
> (cannot be applied) when using mysql_fdw because the transaction starts
> with XA START. Right?

I think we can use XA COMMIT ONE PHASE in MySQL, which both prepares
and commits the transaction. If the number of servers that executed
write queries is less than two, the core transaction manager calls
CommitForeignTransaction API with the flag FDWXACT_FLAG_ONEPHASE. That
way, mysql_fdw can execute XA COMMIT ONE PHASE instead of XA PREPARE,
following XA END. On the other hand, when the number of such servers
is greater than or equals to two, the core transaction manager calls
PrepareForeignTransaction API and then CommitForeignTransactionAPI
without that flag. In this case, mysql_fdw can execute XA END and XA
PREPARE in PrepareForeignTransaction API call, and then XA COMMIT in
CommitForeignTransaction API call.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services