Re: Support for distributed transactions

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