Re: Transactions involving multiple postgres foreign servers

On Sat, Jan 3, 2015 at 2:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
> > 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.
>
> TBH, I think this is a pretty awful idea.
>
> In the first place, this does little to improve the actual reliability
> of a commit occurring across multiple foreign servers; and in the second
> place it creates a bunch of brand new failure modes, many of which would
> require manual DBA cleanup.
>
> The core of the problem is that this doesn't have anything to do with
> 2PC as it's commonly understood: for that, you need a genuine external
> transaction manager that is aware of all the servers involved in a
> transaction, and has its own persistent state (or at least a way to
> reconstruct its own state by examining the per-server states).
> This patch is not that; in particular it treats the local transaction
> asymmetrically from the remote ones, which doesn't seem like a great
> idea --- ie, the local transaction could still abort after committing
> all the remote ones, leaving you no better off in terms of cross-server
> consistency.
>
> As far as failure modes go, one basic reason why this cannot work as
> presented is that the remote servers may not even have prepared
> transaction support enabled (in fact max_prepared_transactions = 0
> is the default in all supported PG versions). So this would absolutely
> have to be a not-on-by-default option.

Agreed. We can have a per foreign server option, which says whether the
corresponding server can participate in 2PC. A transaction spanning
multiple foreign server with at least one of them not capable of
participating in 2PC will need to be aborted.

> But the bigger issue is that
> leaving it to the DBA to clean up after failures is not a production
> grade solution, *especially* not for prepared transactions, which are
> performance killers if not closed out promptly. So I can't imagine
> anyone wanting to turn this on without a more robust answer than that.
>

I purposefully left that outside this patch, since it involves significant
changes in core. If that's necessary for the first cut, I will work on it.

>
> Basically I think what you'd need for this to be a credible patch would be
> for it to work by changing the behavior only in the PREPARE TRANSACTION
> path: rather than punting as we do now, prepare the remote transactions,
> and report their server identities and gids to an external transaction
> manager, which would then be responsible for issuing the actual commits
> (along with the actual commit of the local transaction). I have no idea
> whether it's feasible to do that without having to assume a particular
> 2PC transaction manager API/implementation.
>

I doubt if a TM would expect a bunch of GIDs in response to PREPARE
TRANSACTION command. Per X/Open xa_prepare() expects an integer return
value, specifying whether the PREPARE succeeded or not and some piggybacked
statuses.

In the context of foreign table under inheritance tree, a single DML can
span multiple foreign servers. All such DMLs will then need to be handled
by an external TM. An external TM or application may not have exact idea as
to which all foreign servers are going to be affected by a DML. Users may
not want to setup an external TM in such cases. Instead they would expect
PostgreSQL to manage such DMLs and transactions all by itself.

As Robert has suggested in his responses, it would be better to enable
PostgreSQL to manage distributed transactions itself.

> It'd be interesting to hear from people who are using 2PC in production
> to find out if this would solve any real-world problems for them, and
> what the details of the TM interface would need to look like to make it
> work in practice.
>
> In short, you can't force 2PC technology on people who aren't using it
> already; while for those who are using it already, this isn't nearly
> good enough as-is.
>
> regards, tom lane
>

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