Re: Transactions involving multiple postgres foreign servers

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

> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)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