Re: Transactions involving multiple postgres foreign servers

Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> On Wed, Jan 7, 2015 at 9:50 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

>> Also, as previously mentioned, it must behave in some reasonable
>> way if a database is not configured to support 2PC, especially
>> since 2PC is off by default in PostgreSQL.

> We can have a per foreign server option, which says whether the
> corresponding server is able to participate in 2PC. A transaction
> spanning multiple foreign server with at least one of them not
> capable of participating in 2PC will be aborted.
>
> Will that work?
>
> In case a user flags a foreign server as capable to 2PC
> incorrectly, I expect the corresponding FDW would raise error
> (either because PREPARE fails or FDW doesn't handle that case)
> and the transaction will be aborted anyway.

That sounds like one way to handle it. I'm not clear on how you
plan to determine whether 2PC is required for a transaction.
(Apologies if it was previously mentioned and I've forgotten it.)

I don't mean to suggest that these problems are insurmountable; I
just think that people often underestimate the difficulty of
writing a distributed transaction manager and don't always
recognize the problems that it will cause if all of the failure
modes are not considered and handled.

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