Re: Transactions involving multiple postgres foreign servers - Mailing list pgsql-hackers

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.

Suggestions and comments are helpful to implement this feature.

Regards,

Vinayak Pokale