While looking at the patch for supporting inheritance on foreign tables, I noticed that if a transaction makes changes to more than two foreign servers the current implementation in postgres_fdw doesn't make sure that either all of them rollback or all of them commit their changes, IOW there is a possibility that some of them commit their changes while others rollback theirs.
PFA patch which uses 2PC to solve this problem. In pgfdw_xact_callback() at XACT_EVENT_PRE_COMMIT event, it sends prepares the transaction at all the foreign postgresql servers and at XACT_EVENT_COMMIT or XACT_EVENT_ABORT event it commits or aborts those transactions resp.
The logic to craft the prepared transaction ids is rudimentary and I am open to suggestions for the same. I have following goals in mind while crafting the transaction ids
1. Minimize the chances of crafting a transaction id which would conflict with a concurrent transaction id on that foreign server.
2. Because of a limitation described later, DBA/user should be able to identify the server which originated a remote transaction.
More can be found in comments above function pgfdw_get_prep_xact_id() in the patch.
Limitations ---------------
1. After a transaction has been prepared on foreign server, if the connection to that server is lost before the transaction is rolled back or committed on that server, the transaction remains in prepared state forever. Manual intervention would be needed to clean up such a transaction (Hence the goal 2 above). Automating this process would require significant changes to the transaction manager, so, left out of this patch, which I thought would be better right now. If required, I can work on that part in this patch itself.
2. 2PC is needed only when there are more than two foreign servers involved in a transaction. Transactions on a single foreign server are handled well right now. So, ideally, the code should detect if there are more than two foreign server are involved in the transaction and only then use 2PC. But I couldn't find a way to detect that without changing the transaction manager.
--
Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company