Thread: dblink: rollback transaction

Oleg Lebedev said:
> Is there a way to rollback a dblink transaction?
> Say, I delete some data from the remote database, but I don't want this
> to be visible untill the data is inserted in the current database. And
> if the insertion of data in the current database throws an error, I want
> to rollback the dblink transaction, which should restore data in its
> original remote location.

What about doing the insert first, and then issuing the delete via the
dblink? If the insert fails and the transaction rolls back then the
deletion in the remote database will never have been done.

John Sidney-Woollett

John,

The example I provided was for illustrational purposes only :) The
problem that I am trying to solve is more complex. Basically, I am
trying to propagate remote data from remote tables and install it in the
local tables. I do this operation in a loop as follows:

For j IN all_tables LOOP
    1. Bring remote data from remote_tables[j] (using dblink)
    2. Insert received data in local_tables[j]
    3. Delete data from remote_table[j] (using dblink)
END LOOP

Suppose I successfully ran the first loop iteration, but the second
iteration caused step 2 to through a "duplicate key" exception. This
will cause the effects of both loop iterations to roll back. However,
only local operations (step 2), but not the remote operations (step 3)
are rolled back. This causes the data brought and installed into the
first table to be deleted locally (i.e. step 2 of the first iteration is
rolled back), but not restored remotely (i.e. step 3 of the first
iteration is NOT rolled back). Therefore, I lose data for the first
table completely both in the local and remote locations.

Is there any way to roll back a remote dblink Xaction? Does anyone have
a better solution for my problem?

Thanks.

Oleg


-----Original Message-----
From: John Sidney-Woollett [mailto:johnsw@wardbrook.com]
Sent: Thursday, February 05, 2004 12:49 AM
To: Oleg Lebedev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dblink: rollback transaction


Oleg Lebedev said:
> Is there a way to rollback a dblink transaction?
> Say, I delete some data from the remote database, but I don't want
> this to be visible untill the data is inserted in the current
> database. And if the insertion of data in the current database throws
> an error, I want to rollback the dblink transaction, which should
> restore data in its original remote location.

What about doing the insert first, and then issuing the delete via the
dblink? If the insert fails and the transaction rolls back then the
deletion in the remote database will never have been done.

John Sidney-Woollett

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

Oleg Lebedev said:
> The example I provided was for illustrational purposes only :)

I figured!

> Is there any way to roll back a remote dblink Xaction? Does anyone have
> a better solution for my problem?

Don't know enough to say or guess, sorry!

I think PG badly needs nested transaction support... ;)

John Sidney-Woollett

John Sidney-Woollett wrote:
>
> I think PG badly needs nested transaction support... ;)

I think that is a main take-away here. You should not try to depend on
dblink as a robust replication solution. Perhaps if postgres had
two-phase commit and nested transactions, but not at the moment.

That said, depending on how you are implementing the loop in your
pseudo-code, you might be able to get closer by using persistent dblink
connections, and starting a transaction on the remote side before
starting the local transaction and running your plpgsql function (or
whatever it is you're running). If the local transaction fails, send an
ABORT to the remote side before closing the connection. However I can't
offhand think of a way to do that in an automated fashion.

Joe

>>I think that is a main take-away here. You should not try to depend on

>>dblink as a robust replication solution. Perhaps if postgres had
>>two-phase commit and nested transactions, but not at the moment.

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

>>That said, depending on how you are implementing the loop in your
>>pseudo-code, you might be able to get closer by using persistent
dblink
>>connections, and starting a transaction on the remote side before
>>starting the local transaction and running your plpgsql function (or
>>whatever it is you're running). If the local transaction fails, send
an
>>ABORT to the remote side before closing the connection. However I
can't
>>offhand think of a way to do that in an automated fashion.

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

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?

Thanks.

Oleg

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

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




Index: contrib/dblink/dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.c,v
retrieving revision 1.29
diff -c -r1.29 dblink.c
*** contrib/dblink/dblink.c    28 Nov 2003 05:03:01 -0000    1.29
--- contrib/dblink/dblink.c    5 Feb 2004 19:49:00 -0000
***************
*** 135,140 ****
--- 135,150 ----
                       errmsg("%s", p2), \
                       errdetail("%s", msg))); \
      } while (0)
+ #define DBLINK_RES_ERROR_AS_NOTICE(p2) \
+     do { \
+             msg = pstrdup(PQerrorMessage(conn)); \
+             if (res) \
+                 PQclear(res); \
+             ereport(NOTICE, \
+                     (errcode(ERRCODE_SYNTAX_ERROR), \
+                      errmsg("%s", p2), \
+                      errdetail("%s", msg))); \
+     } while (0)
  #define DBLINK_CONN_NOT_AVAIL \
      do { \
          if(conname) \
***************
*** 731,739 ****
      if (!res ||
          (PQresultStatus(res) != PGRES_COMMAND_OK &&
           PQresultStatus(res) != PGRES_TUPLES_OK))
!         DBLINK_RES_ERROR("sql error");

!     if (PQresultStatus(res) == PGRES_COMMAND_OK)
      {
          /* need a tuple descriptor representing one TEXT column */
          tupdesc = CreateTemplateTupleDesc(1, false);
--- 741,762 ----
      if (!res ||
          (PQresultStatus(res) != PGRES_COMMAND_OK &&
           PQresultStatus(res) != PGRES_TUPLES_OK))
!     {
!         DBLINK_RES_ERROR_AS_NOTICE("sql error");
!
!         /* need a tuple descriptor representing one TEXT column */
!         tupdesc = CreateTemplateTupleDesc(1, false);
!         TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
!                            TEXTOID, -1, 0, false);

!         /*
!          * and save a copy of the command status string to return as our
!          * result tuple
!          */
!         sql_cmd_status = GET_TEXT("ERROR");
!
!     }
!     else if (PQresultStatus(res) == PGRES_COMMAND_OK)
      {
          /* need a tuple descriptor representing one TEXT column */
          tupdesc = CreateTemplateTupleDesc(1, false);

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.

The patch is against 7.5devel, but it ought to apply against 7.4. I
can't remember if 7.3 supported named persistent connections
...[looks]... doesn't look like it, so you'll need 7.4 at least. I
haven't thought through the possible negative side-effects of this
change yet -- let me know how it goes for you if you try it.

Thanks,

Joe

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

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