Re: dblink: rollback transaction - Mailing list pgsql-general

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