Skip site navigation (1) Skip section navigation (2)

Re: Transactions involving multiple postgres foreign servers

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
To: Vinayak Pokale <vinpokale(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(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-08-26 05:52:36
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale <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.
> COMMIT/ABORT on foreign server which supports 2PC.
>     b. Manage information of foreign prepared transactions resolver
> Masahiko Sawada will post the 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.

Feedback and suggestion are very welcome.


Masahiko Sawada

Attachment: 0001-Support-transaction-with-foreign-servers.patch
Description: text/plain (97.0 KB)
Attachment: 0002-Support-2PC-for-postgres_fdw.patch
Description: text/plain (26.0 KB)

In response to


pgsql-hackers by date

Next:From: Ashutosh BapatDate: 2016-08-26 06:03:02
Subject: Re: Transactions involving multiple postgres foreign servers
Previous:From: Victor WagnerDate: 2016-08-26 04:47:30
Subject: Re: Patch: Implement failover on libpq connect level.

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group