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)? (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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
