Re: Transactions involving multiple postgres foreign servers

From: vinayak <Pokale_Vinayak_q3(at)lab(dot)ntt(dot)co(dot)jp>
To: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Cc: Vinayak Pokale <vinpokale(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Subject: Re: Transactions involving multiple postgres foreign servers
Date: 2016-09-07 01:54:46
Message-ID: 924d803f-ac90-6920-e87c-4909d5d35f2c@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2016/08/26 15:13, Ashutosh Bapat wrote:
>
>
> On Fri, Aug 26, 2016 at 11:37 AM, Masahiko Sawada
> <sawada(dot)mshk(at)gmail(dot)com <mailto:sawada(dot)mshk(at)gmail(dot)com>> wrote:
>
> On Fri, Aug 26, 2016 at 3:03 PM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com
> <mailto:ashutosh(dot)bapat(at)enterprisedb(dot)com>> wrote:
> >
> >
> > On Fri, Aug 26, 2016 at 11:22 AM, Masahiko Sawada
> <sawada(dot)mshk(at)gmail(dot)com <mailto:sawada(dot)mshk(at)gmail(dot)com>>
> > wrote:
> >>
> >> On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale
> <vinpokale(at)gmail(dot)com <mailto:vinpokale(at)gmail(dot)com>>
> >> wrote:
> >> > Hi All,
> >> >
> >> > Ashutosh proposed the feature 2PC for FDW for achieving
> atomic commits
> >> > across multiple foreign servers.
> >> > If a transaction make changes to more than two foreign
> servers the
> >> > current
> >> > implementation in postgres_fdw doesn't make sure that either
> all of them
> >> > commit or all of them rollback their changes.
> >> >
> >> > We (Masahiko Sawada and me) reopen this thread and trying to
> contribute
> >> > in
> >> > it.
> >> >
> >> > 2PC for FDW
> >> > ============
> >> > The patch provides support for atomic commit for transactions
> involving
> >> > foreign servers. when the transaction makes changes to
> foreign servers,
> >> > either all the changes to all the foreign servers commit or
> rollback.
> >> >
> >> > The new patch 2PC for FDW include the following things:
> >> > 1. The patch 0001 introduces a generic feature. All kinds of
> FDW that
> >> > support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc.
> can involve
> >> > in
> >> > the transaction.
> >> >
> >> > Currently we can push some conditions down to shard nodes,
> especially in
> >> > 9.6
> >> > the directly modify feature has
> >> > been introduced. But such a transaction modifying data on
> shard node is
> >> > not
> >> > executed surely.
> >> > Using 0002 patch, that modify is executed with 2PC. It means
> that we
> >> > almost
> >> > can provide sharding solution using
> >> > multiple PostgreSQL server (one parent node and several
> shared node).
> >> >
> >> > For multi master, we definitely need transaction manager but
> transaction
> >> > manager probably can use this 2PC for FDW feature to manage
> distributed
> >> > transaction.
> >> >
> >> > 2. 0002 patch makes postgres_fdw possible to use 2PC.
> >> >
> >> > 0002 patch makes postgres_fdw to use below APIs. These APIs
> are generic
> >> > features which can be used by all kinds of FDWs.
> >> >
> >> > a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED
> instead of
> >> > COMMIT/ABORT on foreign server which supports 2PC.
> >> > b. Manage information of foreign prepared transactions
> resolver
> >> >
> >> > Masahiko Sawada will post the patch.
> >> >
> >> >
> >>
> >
> > Thanks Vinayak and Sawada-san for taking this forward and basing
> your work
> > on my patch.
> >
> >>
> >> Still lot of work to do but attached latest patches.
> >> These are based on the patch Ashutosh posted before, I revised
> it and
> >> divided into two patches.
> >> Compare with original patch, patch of pg_fdw_xact_resolver and
> >> documentation are lacked.
> >
> >
> > I am not able to understand the last statement.
>
> Sorry to confuse you.
>
> > Do you mean to say that your patches do not have
> pg_fdw_xact_resolver() and
> > documentation that my patches had?
>
> Yes.
> I'm confirming them that your patches had.
>
>
> Thanks for the clarification. I had added pg_fdw_xact_resolver() to
> resolve any transactions which can not be resolved immediately after
> they were prepared. There was a comment from Kevin (IIRC) that leaving
> transactions unresolved on the foreign server keeps the resources
> locked on those servers. That's not a very good situation. And nobody
> but the initiating server can resolve those. That functionality is
> important to make it a complete 2PC solution. So, please consider it
> to be included in your first set of patches.
The attached patch included pg_fdw_xact_resolver.

Regards,
Vinayak Pokale
NTT Open Source Software Center

Attachment Content-Type Size
0003-pg-fdw-xact-resolver.patch application/x-download 11.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2016-09-07 02:01:59 Re: [PATCH] COPY vs \copy HINT
Previous Message Amit Langote 2016-09-07 01:46:06 Re: Let file_fdw access COPY FROM PROGRAM