Re: Transactions involving multiple postgres foreign servers

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Andres is talking in my other ear suggesting that we ought to
> reuse the 2PC infrastructure to do all this.

If you mean that the primary transaction and all FDWs in the
transaction must use 2PC, that is what I was saying, although
apparently not clearly enough. All nodes *including the local one*
must be prepared and committed with data about the nodes saved
safely off somewhere that it can be read in the event of a failure
of any of the nodes *including the local one*. Without that, I see
this whole approach as a train wreck just waiting to happen.

I'm not really clear on the mechanism that is being proposed for
doing this, but one way would be to have the PREPARE of the local
transaction be requested explicitly and to have that cause all FDWs
participating in the transaction to also be prepared. (That might
be what Andres meant; I don't know.) That doesn't strike me as the
only possible mechanism to drive this, but it might well be the
simplest and cleanest. The trickiest bit might be to find a good
way to persist the distributed transaction information in a way
that survives the failure of the main transaction -- or even the
abrupt loss of the machine it's running on.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company