support for distributed transactions

"Brian Oki (boki)" <bokicisco.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 foonode2.acme.com ....
>     insert into foonode3.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

---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an
appropriate
       subscribe-nomail command to majordomopostgresql.org so that your
       message can get through to the mailing list cleanly

--- "Brian Oki (boki)" <bokicisco.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://pgfoun
dry.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.

---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster