Re: Transactions involving multiple postgres foreign servers

On Thu, Jan 8, 2015 at 7:02 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

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

Any transaction involving more than one server (including local one, I
guess), will require two PC. A transaction may modify and access remote
database but not local one. In such a case, the state of local transaction
doesn't matter once the remote transaction is committed or rolled back.

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

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company