Re: Transactions involving multiple postgres foreign servers, take 2 - Mailing list pgsql-hackers

On 2020/09/08 12:03, Amit Kapila wrote:
> On Tue, Sep 8, 2020 at 8:05 AM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
>>
>> On 2020/09/08 10:34, Amit Kapila wrote:
>>> On Mon, Sep 7, 2020 at 2:29 PM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
>>>>
>>>> IMO it's not easy to commit this 2PC patch at once because it's still large
>>>> and complicated. So I'm thinking it's better to separate the feature into
>>>> several parts and commit them gradually.
>>>>
>>>
>>> Hmm, I don't see that we have a consensus on the design and or
>>> interfaces of this patch and without that proceeding for commit
>>> doesn't seem advisable. Here are a few points which I remember offhand
>>> that require more work.
>>
>> Thanks!
>>
>>> 1. There is a competing design proposed and being discussed in another
>>> thread [1] for this purpose. I think both the approaches have pros and
>>> cons but there doesn't seem to be any conclusion yet on which one is
>>> better.
>>
>> I was thinking that [1] was discussing global snapshot feature for
>> "atomic visibility" rather than the solution like 2PC for "atomic commit".
>> But if another approach for "atomic commit" was also proposed at [1],
>> that's good. I will check that.
>>
> 
> Okay, that makes sense.

I read Alexey's 2PC patch (0001-Add-postgres_fdw.use_twophase-GUC-to-use-2PC.patch)
proposed at [1]. As Alexey told at that thread, there are two big differences
between his patch and Sawada-san's; 1) whether there is the resolver process
for foreign transactions, 2) 2PC logic is implemented only inside postgres_fdw
or both FDW and PostgreSQL core.

I think that 2) is the first decision point. Alexey's 2PC patch is very simple
and all the 2PC logic is implemented only inside postgres_fdw. But this
means that 2PC is not usable if multiple types of FDW (e.g., postgres_fdw
and mysql_fdw) participate at the transaction. This may be ok if we implement
2PC feature only for PostgreSQL sharding using postgres_fdw. 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?

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?

[1]
https://postgr.es/m/3ef7877bfed0582019eab3d462a43275@postgrespro.ru

Regards,

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