Re: Transactions involving multiple postgres foreign servers

On 2017/01/16 17:35, Masahiko Sawada wrote:
> On Fri, Jan 13, 2017 at 3:48 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> On Fri, Jan 13, 2017 at 3:20 PM, Ashutosh Bapat
>> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>>>> Long time passed since original patch proposed by Ashutosh, so I
>>>> explain again about current design and functionality of this feature.
>>>> If you have any question, please feel free to ask.
>>> Thanks for the summary.
>>>
>>>> Parameters
>>>> ==========
>>> [ snip ]
>>>
>>>> Cluster-wide atomic commit
>>>> =======================
>>>> Since the distributed transaction commit on foreign servers are
>>>> executed independently, the transaction that modified data on the
>>>> multiple foreign servers is not ensured that transaction did either
>>>> all of them commit or all of them rollback. The patch adds the
>>>> functionality that guarantees distributed transaction did either
>>>> commit or rollback on all foreign servers. IOW the goal of this patch
>>>> is achieving the cluster-wide atomic commit across foreign server that
>>>> is capable two phase commit protocol.
>>> In [1], I proposed that we solve the problem of supporting PREPARED
>>> transactions involving foreign servers and in subsequent mail Vinayak
>>> agreed to that. But this goal has wider scope than that proposal. I am
>>> fine widening the scope, but then it would again lead to the same
>>> discussion we had about the big picture. May be you want to share
>>> design (or point out the parts of this design that will help) for
>>> solving smaller problem and tone down the patch for the same.
>>>
>> Sorry for confuse you. I'm still focusing on solving only that
>> problem. What I was trying to say is that I think that supporting
>> PREPARED transaction involving foreign server is the means, not the
>> end. So once we supports PREPARED transaction involving foreign
>> servers we can achieve cluster-wide atomic commit in a sense.
>>
> Attached updated patches. I fixed some bugs and add 003 patch that
> adds TAP test for foreign transaction.
> 003 patch depends 000 and 001 patch.
>
> Please give me feedback.

I have tested prepared transactions with foreign servers but after
preparing the transaction
the following error occur infinitely.
Test:
=====
=#BEGIN;
=#INSERT INTO ft1_lt VALUES (10);
=#INSERT INTO ft2_lt VALUES (20);
=#PREPARE TRANSACTION 'prep_xact_with_fdw';

2017-01-18 15:09:48.378 JST [4312] ERROR: function pg_fdw_resolve()
does not exist at character 8
2017-01-18 15:09:48.378 JST [4312] HINT: No function matches the given
name and argument types. You might need to add explicit type casts.
2017-01-18 15:09:48.378 JST [4312] QUERY: SELECT pg_fdw_resolve()
2017-01-18 15:09:48.378 JST [29224] LOG: worker process: foreign
transaction resolver (dbid 13119) (PID 4312) exited with exit code 1
.....

If we check the status on another session then it showing the status as
prepared.
=# select * from pg_fdw_xacts;
dbid | transaction | serverid | userid | status | identifier
-------+-------------+----------+--------+----------+------------------------

13119 | 1688 | 16388 | 10 | prepared |
px_2102366504_16388_10
13119 | 1688 | 16389 | 10 | prepared |
px_749056984_16389_10
(2 rows)

I think this is a bug.

Regards,
Vinayak Pokale
NTT Open Source Software Center