Re: Transactions involving multiple postgres foreign servers

On Tue, Jan 6, 2015 at 11:55 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Mon, Jan 5, 2015 at 3:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Well, we intentionally didn't couple the FDW stuff closely into
> > transaction commit, because of the thought that the "far end" would not
> > necessarily have Postgres-like transactional behavior, and even if it did
> > there would be about zero chance of having atomic commit with a
> > non-Postgres remote server. postgres_fdw is a seriously bad starting
> > point as far as that goes, because it encourages one to make assumptions
> > that can't possibly work for any other wrapper.
>
> Atomic commit is something that can potentially be supported by many
> different FDWs, as long as the thing on the other end supports 2PC.
> If you're talking to Oracle or DB2 or SQL Server, and it supports 2PC,
> then you can PREPARE the transaction and then go back and COMMIT the
> transaction once it's committed locally.

Getting a cluster-wide
> *snapshot* is probably a PostgreSQL-only thing requiring much deeper
> integration, but I think it would be sensible to leave that as a
> future project and solve the simpler problem first.
>
> > I think the idea I sketched upthread of supporting an external
> transaction
> > manager might be worth pursuing, in that it would potentially lead to
> > having at least an approximation of atomic commit across heterogeneous
> > servers.
>
> An important threshold question here is whether we want to rely on an
> external transaction manager, or build one into PostgreSQL. As far as
> this particular project goes, there's nothing that can't be done
> inside PostgreSQL. You need a durable registry of which transactions
> you prepared on which servers, and which XIDs they correlate to. If
> you have that, then you can use background workers or similar to go
> retry commits or rollbacks of prepared transactions until it works,
> even if there's been a local crash meanwhile.
>

> Alternatively, you could rely on an external transaction manager to do
> all that stuff. I don't have a clear sense of what that would entail,
> or how it might be better or worse than rolling our own. I suspect,
> though, that it might amount to little more than adding a middle man.
> I mean, a third-party transaction manager isn't going to automatically
> know how to commit a transaction prepared on some foreign server using
> some foreign data wrapper. It's going to be have to be taught that if
> postgres_fdw leaves a transaction in-medias-res on server OID 1234,
> you've got to connect to the target machine using that foreign
> server's connection parameters, speak libpq, and issue the appropriate
> COMMIT TRANSACTION command. And similarly, you're going to need to
> arrange to notify it before preparing that transaction so that it
> knows that it needs to request the COMMIT or ABORT later on. Once
> you've got all of that infrastructure for that in place, what are you
> really gaining over just doing it in PostgreSQL (or, say, a contrib
> module thereto)?
>

Thanks Robert for giving high level view of system needed for PostgreSQL to
be a transaction manager by itself. Agreed completely.

>
> (I'm also concerned that an external transaction manager might need
> the PostgreSQL client to be aware of it, whereas what we'd really like
> here is for the client to just speak PostgreSQL and be happy that its
> commits no longer end up half-done.)
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

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