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

Joe,

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.
Thanks.

Oleg

-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Thursday, February 05, 2004 11:50 AM
To: Oleg Lebedev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dblink: rollback transaction


Oleg Lebedev wrote:
> Agreed. I wonder if I should simulate local Xactions by using local
> dblink calls? What do you think, Joe?

It is an interesting thought. Withing a single plpgsql function, open
one local and one remote persistent, named dblink connection. Start a
transaction in each. Go into your loop. Here's the problem -- I don't
know how you can programmatically detect an error. Try playing with
dblink_exec for this. If you can detect an error condition, you can then

ABORT both transactions.

> So, is it actually possible to use BEGIN; .. COMMIT; statement with
> dblink?

Sure. Use a named persistent connection. Then issue a BEGIN just like
any other remote SQL statement (might be best to use dblink_exec with
this also).

> Even if I start the remote Xaction before the local one starts, there
> is no way for me to catch an exception thrown by the local Xaction. I
> don't think Pl/PgSQL supports exceptions. So, if the local Xaction
> throws an exception then the whole process terminates.
>
> Ideas?

[runs off to try a few things...]

I played with this a bit, and found that with some minor changes to
dblink_exec(), I can get the behavior we want, I think.

===============================================================
Here's the SQL:
===============================================================

\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');

\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);
   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;


===============================================================
Here's the test:
===============================================================
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();
  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)

===============================================================

Patch attached. Thoughts?

Joe

*************************************
This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.
*************************************