Re: Transactions involving multiple postgres foreign servers, take 2

On 2020/07/17 20:04, Masahiko Sawada wrote:
> On Fri, 17 Jul 2020 at 14:22, tsunakawa.takay@fujitsu.com
> <tsunakawa.takay@fujitsu.com> wrote:
>>
>> From: Masahiko Sawada <masahiko.sawada@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