Re: Support for distributed transactions in 8.2.5

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(at)node2(dot)acme(dot)com <BLOCKED::mailto:foo(at)node2(dot)acme(dot)com>
....
insert into foo(at)node3(dot)acme(dot)com <mailto:foo(at)node3(dot)acme(dot)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:
> 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.

It doesn't, there isn't, and no.

I'm afraid the 2-phase commit in 8.2 is there as a building-block for
this sort of thing, rather than a complete solution in itself.

I believe there are some Java tools that might help you here.

--
Richard Huxton
Archonet Ltd

On Thursday 11 October 2007 11:50, Richard Huxton wrote:
> Brian Oki (boki) 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? It's sort of like the synchronous multi-master
> > replication mentioned in Chapter 24. I'm looking for an integrated
> > solution.
>
> It doesn't, there isn't, and no.
>

I am thinking that the OP might be thinking about pl/proxy, which you
interface at the sql level like other procedures, and it does some magic
behind the scenes to farm queries where they are supposed to go. Granted this
doesn't really support acid safe distributed dml, but might be what he was
thinking?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL