Re: Transactions involving multiple postgres foreign servers - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: Transactions involving multiple postgres foreign servers
Date
Msg-id CAFjFpRcs0bHbZo-Gofr=nZULagGhvriD3jWCiRnRENkRRX-gXg@mail.gmail.com
Whole thread Raw
In response to Re: Transactions involving multiple postgres foreign servers  (Kevin Grittner <kgrittn@ymail.com>)
Responses Re: Transactions involving multiple postgres foreign servers
List pgsql-hackers


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

> I don't see why would my patch cause inconsistencies. It can
> cause dangling PREPAREd transactions and I have already
> acknowledged that fact.
>
> Am I missing something?

To me that is the big problem.  Where I have run into ad hoc
distributed transaction managers it has usually been because a
crash left prepared transactions dangling, without cleaning them up
when the transaction manager was restarted.  This tends to wreak
havoc one way or another.

If we are going to include a distributed transaction manager with
PostgreSQL, it *must* persist enough information about the
transaction ID and where it is used in a way that will survive a
subsequent crash before beginning the PREPARE on any of the
systems.

Thanks a lot. I hadn't thought of this.
 
After all nodes are PREPAREd it must flag that persisted
data to indicate that it is now at a point where ROLLBACK is no
longer an option.  Only then can it start committing the prepared
transactions.  After the last node is committed it can clear this
information.  On start-up the distributed transaction manager must
check for any distributed transactions left "in progress" and
commit or rollback based on the preceding; doing retries
indefinitely until it succeeds or is told to stop.


Agreed.
 
Doing this incompletely (i.e., not identifying and correctly
handling the various failure modes) is IMO far worse than not
attempting it.  If we could build in something that did this
completely and well, that would be a cool selling point; but let's
not gloss over the difficulties.  We must recognize how big a
problem it would be to include a low-quality implementation.

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.


I described one possibility in my reply to Tom's mail. Let me repeat it here.

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.
 
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



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

pgsql-hackers by date:

Previous
From: Ali Akbar
Date:
Subject: Re: PATCH: decreasing memory needlessly consumed by array_agg
Next
From: Dean Rasheed
Date:
Subject: Re: Possible typo in create_policy.sgml