On Tue, Jan 6, 2015 at 11:55 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Mon, Jan 5, 2015 at 3:23 PM, Tom Lane <t...@sss.pgh.pa.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
