On 2020/07/17 20:04, Masahiko Sawada wrote:
On Fri, 17 Jul 2020 at 14:22, tsunakawa.ta...@fujitsu.com
<tsunakawa.ta...@fujitsu.com> wrote:
From: Masahiko Sawada <masahiko.saw...@2ndquadrant.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?
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?
If that's the "optimization" only for postgres_fdw, maybe it's better to
get rid of that "optimization" from the first patch, to make the patch simpler.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
