Re: Transactions involving multiple postgres foreign servers

On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale <vinpokale@gmail.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.
>
>

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.

Regards,

--
Masahiko Sawada