Re: Transactions involving multiple postgres foreign servers

On Thu, Jan 19, 2017 at 5:44 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Thu, Jan 19, 2017 at 4:04 PM, vinayak
> <Pokale_Vinayak_q3(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>
>> 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.
>>
>
> Thank you for reviewing!
>
> I think this is a bug of pg_fdw_resolver contrib module. I had
> forgotten to change the SQL executed by pg_fdw_resolver process.
> Attached latest version 002 patch.
>

As previous version patch conflicts to current HEAD, attached updated
version patches. Also I fixed some bugs in pg_fdw_xact_resolver and
added some documentations.
Please review it.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center