Re: Using tables in other PostGreSQL database

> Also, 2PC is subject to unresolved transactions (or something like
that).
>
> > > Even in Oracle you don't have cross db queries.
> >
> > On the contrary you do. You can refer to objects in another
database by
> > OBJECT_NAME(at)DBLINK_NAME, very useful to mix local and remote data
in no
> > time. DBLINK_NAME represents a connection to another database.
> > What you don't have is OTHERDB.OBJECT_NAME to refer to a different
> > database within the same instance, because there is only one
database
> > in an Oracle instance.
>
> What you are talking about are cross schema references, not cross db.
> Oracle instances can have > 1 database, it's just not that common. I
> know this because we had an internal instance at the last company I
> worked at that had 2 databases in it, each with their own schemas. Or
> maybe they somehow had two instances of oracle running on the same
> box. I'm no oracle expert, I'm just reporting what I saw with my own
> eye.

An Oracle instance is equal to a single database except for Oracle RAC.
With Oracle RAC, there is an instance per node so a two node RAC would
have two instances running. But to a developer, these two instances
look like one physical database.

You can have multiple instances running on the same box. You just need
to configure the Oracle listener to listen on two different ports; one
for each instance. The two instances are not tied together at all. You
could also run two separate Oracle homes and basically have two sets of
binaries installed and run two listeners.

Using an Oracle DB link, you can link to another database with the
@db_name syntax and get two phase commits.

HOWEVER, you can achieve a two phase commit in PostgreSQL with db_link
and using basic exception handling.

Example:
create table log (update_datetime timestamp);

create or replace function fn_test (p_fail boolean) returns void as
$$
declare
v_sql varchar;
v_int int;

begin
perform dblink_connect('pg', 'dbname=postgres user=scott
password=tiger host=localhost');

v_sql := 'begin;';
perform dblink_exec('pg', v_sql, false);

v_sql := 'insert into log values (now())';
perform dblink_exec('pg', v_sql, false);

if p_fail then
v_int := 1/0;
end if;

v_sql := 'commit;';
perform dblink_exec('pg', v_sql, false);

perform dblink_disconnect('pg');
exception
when others then
v_sql := 'rollback;';
perform dblink_exec('pg', v_sql, false);
perform dblink_disconnect('pg');
raise exception '%', sqlerrm;
end;
$$
language 'plpgsql';

Now that is a basic function that will insert data into the postgres
database via a dblink. If you pass in fail, it will hit "division by
zero" and rollback both the linked transaction and the primary
transaction of the function.

select fn_test(false);
select * from log;
--you see a new row

select fn_test(true)
--ERROR: division by zero
select * from log;
--you see that a new row wasn't inserted.

Jon