Re: Transactions involving multiple postgres foreign servers, take 2

On 2020/09/10 10:13, tsunakawa(dot)takay(at)fujitsu(dot)com wrote:
> Alexey-san, Sawada-san,
> cc: Fujii-san,
>
>
> From: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
>> But if we
>> implement 2PC as the improvement on FDW independently from PostgreSQL
>> sharding, I think that it's necessary to support other FDW. And this is our
>> direction, isn't it?
>
> I understand the same way as Fujii san. 2PC FDW is itself useful, so I think we should pursue the tidy FDW interface and good performance withinn the FDW framework. "tidy" means that many other FDWs should be able to implement it. I guess XA/JTA is the only material we can use to consider whether the FDW interface is good.

Originally start(), commit() and rollback() are supported as FDW interfaces. With his patch, prepare() is supported. What other interfaces need to be supported per XA/JTA?

As far as I and Sawada-san discussed this upthread, to support MySQL, another type of start() would be necessary to issue "XA START id" command. end() might be also necessary to issue "XA END id", but that command can be issued via prepare() together with "XA PREPARE id".

I'm not familiar with XA/JTA and XA transaction interfaces on other major DBMS. So I'd like to know what other interfaces are necessary additionally?

>
>
>> Sawada-san's patch supports that case by implememnting some conponents
>> for that also in PostgreSQL core. For example, with the patch, all the remote
>> transactions that participate at the transaction are managed by PostgreSQL
>> core instead of postgres_fdw layer.
>>
>> Therefore, at least regarding the difference 2), I think that Sawada-san's
>> approach is better. Thought?
>
> I think so. Sawada-san's patch needs to address the design issues I posed before digging into the code for thorough review, though.
>
> BTW, is there something Sawada-san can take from Alexey-san's patch? I'm concerned about the performance for practical use. Do you two have differences in these points, for instance?

IMO Sawada-san's version of 2PC is less performant, but it's because
his patch provides more functionality. For example, with his patch,
WAL is written to automatically complete the unresolve foreign transactions
in the case of failure. OTOH, Alexey patch introduces no new WAL for 2PC.
Of course, generating more WAL would cause more overhead.
But if we need automatic resolution feature, it's inevitable to introduce
new WAL whichever the patch we choose.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION