Обсуждение: Support for distributed transactions
Hello,
Tom Lane tells me I should contact folks on this email alias to find out about external XA support available in the Java environment for supporting distributed transactions in Postgres. Here's the original email I sent to the general alias. Any suggestions would be greatly appreciated.
Brian Oki
I've read through the relevant documentation on distributed transactions for PostgreSQL 8.2.5 but it leaves me with more questions than answers. It is unclear to me how SQL statements can be executed at remote nodes from a single coordinator and then use distributed two-phase commit (via 'prepare transaction tid' and 'commit prepared'). I worked at Oracle in the distributed database group and could do things like the following using PL/SQL, where we insert the same row into the same table on three different nodes, including the local one:
insert into foo ....
insert into foo@node2.acme.com ....
insert into foo@node3.acme.com ....
COMMIT
This sequence will insert a row into all three tables and do the distributed atomic commitment.
My question is this: How does PostgreSQL 8.2.5 execute DML statements (insert, update, delete, select) on remote nodes as part of the same transaction? Where is the syntax specified? Or, is there a different model supported? It's sort of like the synchronous multi-master replication mentioned in Chapter 24. I'm looking for an integrated solution.
Brian Oki, Ph.D.
Cisco Systems, Inc.
Brian Oki (boki) wrote: > It is unclear to me how SQL statements can be executed at remote nodes > from a single coordinator and then use distributed two-phase commit (via > 'prepare transaction tid' and 'commit prepared'). I worked at Oracle in > the distributed database group and could do things like the following > using PL/SQL, where we insert the same row into the same table on three > different nodes, including the local one: > insert into foo .... > insert into foo@node2.acme.com <blocked::mailto:foo@node2.acme.com> > .... > insert into foo@node3.acme.com <mailto:foo@node3.acme.com> .... > COMMIT > This sequence will insert a row into all three tables and do the > distributed atomic commitment. > > My question is this: How does PostgreSQL 8.2.5 execute DML statements > (insert, update, delete, select) on remote nodes as part of the same > transaction? Where is the syntax specified? Unfortunately PostgreSQL doesn't have remote or federated queries like that. You can't reference tables in different nodes. PostgreSQL does support two-phase commit, but what you're looking for is much more than that. There is, however, a contrib module called dblink, that you can use to submit SQL queries to remote database, but it's not integrated so that you could do "INSERT foo@remotenode". By using dblink in update rules on a view you can emulate that to some extent. But dblink won't do two-phase commit, so you'll find that the updates to remote tables are committed as soon as the statement finishes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com