Re: Transactions involving multiple postgres foreign servers

On Thu, Jan 8, 2015 at 10:19 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> 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.

Clearly, all the nodes other than the local one need to use 2PC. I am
unconvinced that the local node must write a 2PC state file only to
turn around and remove it again almost immediately thereafter.

> 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.)

We want this to be client-transparent, so that the client just says
COMMIT and everything Just Works.

> 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.

I'd be willing to punt on surviving a loss of the entire machine. But
I'd like to be able to survive an abrupt reboot.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company