Thread: support for distributed transactions
Hi,
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?
Brian Oki, Ph.D.
Cisco Systems, Inc.
"Brian Oki (boki)" <boki@cisco.com> writes:
> ... 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
[ shrug... ] Stuff like that is why Oracle is enormously larger, more
complicated, and more expensive than Postgres.
There is no distributed transaction support built into Postgres. There
is two-phase commit, on top of which a transaction manager can be built
to manage distributed transactions externally. This seems like a
reasonable design to me, both because it splits up the concerns and
because it allows distributed transactions across different DBMSes
so long as they all support the 2PC protocol. (Hence, don't hold your
breath waiting for an integrated solution.)
I believe there's some suitable external XA support available in the
Java environment, but I'm pretty clueless on details. The folk in
pgsql-jdbc would probably be able to help you out if that seems like
a reasonable approach for you.
regards, tom lane
--- "Brian Oki (boki)" <boki@cisco.com> wrote: > 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? I am not to sure. If you don't get many good answers, I would repost this question on the PG-general mailing list since it is more active and will have users that can give you a well informed comment. There are two ideas that I have that may or may not be what you are looking for. My first idea is basically replication. You write to the "master" database and the changes are cascaded to various nodes. The two projects that come to mind are slony and mammoth replicator. http://slony.info/ http://www.commandprompt.com/products/mammothreplicator/ The other idea is to write to tables on external server db-clusters. http://pgfoundry.org/projects/dbi-link/ I assume that sql statements issued from dbi-link can be acid compliant, however, I doubt that it supports referential integrity across the various nodes. Regards, Richard Broersma Jr.