Re: dblink: rollback transaction

Oleg Lebedev wrote:
> Your fix is awesome! That's exactly what I need.
> What version of postgres do I need to have installed to try this patch?
> I am on 7.3 now.

BTW, in the last example I neglected to check for errors on the remote
side. For the mail archives, this one is more complete (but it still
probably needs more thought/error checking):

\c remote
drop table foo;
create table foo(f1 int primary key, f2 text);
insert into foo values (1,'a');
insert into foo values (2,'b');
insert into foo values (3,'b');
create table bar(f1 int primary key, f2 int references foo(f1));
insert into bar values (1,3);

\c local
drop table foo;
create table foo(f1 int primary key, f2 text);
--note this is missing on remote side
create unique index uindx1 on foo(f2);

create or replace function test() returns text as '
declare
res text;
tup record;
sql text;
begin
-- leaving out result checking for clarity
select into res dblink_connect(''localconn'',''dbname=local'');
select into res dblink_connect(''remoteconn'',''dbname=remote'');
select into res dblink_exec(''localconn'',''BEGIN'');
select into res dblink_exec(''remoteconn'',''BEGIN'');

for tup in select * from dblink(''remoteconn'',''select * from foo'')
as t(f1 int, f2 text) loop
sql := ''insert into foo values ('' || tup.f1::text || '','''''' ||
tup.f2 || '''''')'';
select into res dblink_exec(''localconn'',sql);
if res = ''ERROR'' then
select into res dblink_exec(''localconn'',''ABORT'');
select into res dblink_exec(''remoteconn'',''ABORT'');
select into res dblink_disconnect(''localconn'');
select into res dblink_disconnect(''remoteconn'');
return ''ERROR'';
else
sql := ''delete from foo where f1 = '' || tup.f1::text;
select into res dblink_exec(''remoteconn'',sql);
if res = ''ERROR'' then
select into res dblink_exec(''localconn'',''ABORT'');
select into res dblink_exec(''remoteconn'',''ABORT'');
select into res dblink_disconnect(''localconn'');
select into res dblink_disconnect(''remoteconn'');
return ''ERROR'';
end if;
end if;
end loop;
select into res dblink_exec(''localconn'',''COMMIT'');
select into res dblink_exec(''remoteconn'',''COMMIT'');
select into res dblink_disconnect(''localconn'');
select into res dblink_disconnect(''remoteconn'');
return ''OK'';
end;
' language plpgsql;

local=# select test();
NOTICE: sql error
DETAIL: ERROR: duplicate key violates unique constraint "uindx1"

CONTEXT: PL/pgSQL function "test" line 15 at select into variables
test
-------
ERROR
(1 row)

local=# select * from foo;
f1 | f2
----+----
(0 rows)

local=# select * from dblink('dbname=remote','select * from foo') as
t(f1 int, f2 text);
f1 | f2
----+----
1 | a
2 | b
3 | b
(3 rows)

local=# drop index uindx1;
DROP INDEX
local=# select test();
NOTICE: sql error
DETAIL: ERROR: update or delete on "foo" violates foreign key
constraint "$1" on "bar"
DETAIL: Key (f1)=(3) is still referenced from table "bar".

CONTEXT: PL/pgSQL function "test" line 24 at select into variables
test
-------
ERROR
(1 row)

local=# select * from foo;
f1 | f2
----+----
(0 rows)

local=# select * from dblink('dbname=remote','select * from foo') as
t(f1 int, f2 text);
f1 | f2
----+----
1 | a
2 | b
3 | b
(3 rows)

local=# \c remote
You are now connected to database "remote".
remote=# delete from bar;
DELETE 1
remote=# \c local
You are now connected to database "local".
local=# select test();
test
------
OK
(1 row)

local=# select * from foo;
f1 | f2
----+----
1 | a
2 | b
3 | b
(3 rows)

local=# select * from dblink('dbname=remote','select * from foo') as
t(f1 int, f2 text);
f1 | f2
----+----
(0 rows)

Requires previously attached patch and Postgres >= 7.4

HTH,

Joe