Hi, all

While trying dblink_exec(), one of dblink()'s functions, I noticed there was an
odd situation: case 1 and case 2 worked well, but case 3 didn't(see below). 
 I hadn't been aware of it so that I only executed BEGIN and END in
dblink_exec() at first . This time, however, I noticed it by executing ROLLBACK.

I'm hoping that dblink_exec() returns something like warning if those who intend
to do transactions make a declaration of blink_exec('dbname=some', 'begin') by mistake.

for example 
  WARNING :You should declare dblink_exec('dbname=some', 'BEGIN; some queries;
COMMIT/ROLLBACK/END;') or use dblink_exec('BEGIN/COMMIT/ROLLBACK/END')
around dblink_exec('some queries')s. If not, your transactions won't work.

Regards,
Masaru Sugawara



> On Fri, 27 Sep 2002 09:35:48 -0700
> Joe Conway <mail@joeconway.com> wrote:
 ...
> The version of dblink in 7.3 (in beta now) has a new function, dblink_exec, 
> which is specifically intended for INSERT/UPDATE/DELETE. If you can, please 
> give the beta a try.
> 
> I have a patch that allows dblink in 7.2 to execute INSERT/UPDATE/DELETE 
> statements. I'll send it to you off-list if you want (let me know), but it 
> would be better if you can wait for 7.3 to be released and use it.
> 
> Joe
 ...
>       query
>       ------------
>       dblink(text,text) RETURNS setof record
>         - returns a set of results from remote SELECT query
>           (Note: comment out in dblink.sql to use deprecated version)

from http://archives.postgresql.org/pgsql-general/2002-09/msg01290.php




-- tables --
$ cd ../postgresql-7.3.b2/contrib/dblink
$ createdb regression_slave
$ createdb regression_master
$ createlang plpgsql regression_master
$ psql regression_slave

\i dblink.sql
CREATE TABLE foo(f1 int, f2 text, f3 text[], PRIMARY KEY (f1,f2));
INSERT INTO foo VALUES(0,'a','{"a0","b0","c0"}');
INSERT INTO foo VALUES(1,'b','{"a1","b1","c1"}');
INSERT INTO foo VALUES(2,'c','{"a2","b2","c2"}');

\connect regression_master;
\i dblink.sql
CREATE TABLE foo(f1 int, f2 text, f3 text[], PRIMARY KEY (f1,f2));
INSERT INTO foo VALUES(0,'a','{"a0","b0","c0"}');
INSERT INTO foo VALUES(1,'b','{"a1","b1","c1"}');
INSERT INTO foo VALUES(2,'c','{"a2","b2","c2"}');


-- case 1. --
  SELECT dblink_connect('dbname=regression_slave');
  SELECT dblink_exec('BEGIN');
  SELECT dblink_exec('INSERT INTO foo VALUES(12,''m'',''{"a12","b12","c12"}'');');
  SELECT dblink_exec('ROLLBACK');     -- success !
  SELECT dblink_disconnect();

-- case 2. --
  SELECT dblink_exec('dbname=regression_slave', 
     'BEGIN;
      INSERT INTO foo VALUES(12,''m'',''{"a12","b12","c12"}'');
      ROLLBACK; 
     ');        -- success !

-- case 3. -- 
  SELECT dblink_exec('dbname=regression_slave', 'BEGIN'); 
  SELECT dblink_exec('dbname=regression_slave',
                'INSERT INTO foo VALUES(12,''m'',''{"a12","b12","c12"}'');');
  SELECT dblink_exec('dbname=regression_slave', 'ROLLBACK'); -- failure !





Sun, 13 Oct 2002 11:23:36 +0900
Masaru Sugawara <rk73@sea.plala.or.jp>
Transactions through dblink_exec()
Masaru Sugawara wrote:
> Hi, all
> 
> While trying dblink_exec(), one of dblink()'s functions, I noticed there was an
> odd situation: case 1 and case 2 worked well, but case 3 didn't(see below). 
>  I hadn't been aware of it so that I only executed BEGIN and END in
> dblink_exec() at first . This time, however, I noticed it by executing ROLLBACK.
> 
> I'm hoping that dblink_exec() returns something like warning if those who intend
> to do transactions make a declaration of blink_exec('dbname=some', 'begin') by mistake.
> 
> for example 
>   WARNING :You should declare dblink_exec('dbname=some', 'BEGIN; some queries;
> COMMIT/ROLLBACK/END;') or use dblink_exec('BEGIN/COMMIT/ROLLBACK/END')
> around dblink_exec('some queries')s. If not, your transactions won't work.

How can dblink() possibly be used safely for non-readonly 
transactions without a full implementation of a two-phase commit 
protocol? What happens when the remote server issues the COMMIT 
and then the local server crashes?

Mike Mascari
mascarm@mascari.com


Sun, 13 Oct 2002 02:15:36 -0400
Mike Mascari <mascarm@mascari.com>
Re: Transactions through dblink_exec()
Masaru Sugawara wrote:
> I'm hoping that dblink_exec() returns something like warning if those who intend
> to do transactions make a declaration of blink_exec('dbname=some', 'begin') by mistake.
> 
> for example 
>   WARNING :You should declare dblink_exec('dbname=some', 'BEGIN; some queries;
> COMMIT/ROLLBACK/END;') or use dblink_exec('BEGIN/COMMIT/ROLLBACK/END')
> around dblink_exec('some queries')s. If not, your transactions won't work.
> 
{...snip...]
> 
> -- case 3. -- 
>   SELECT dblink_exec('dbname=regression_slave', 'BEGIN'); 
>   SELECT dblink_exec('dbname=regression_slave',
>                 'INSERT INTO foo VALUES(12,''m'',''{"a12","b12","c12"}'');');
>   SELECT dblink_exec('dbname=regression_slave', 'ROLLBACK'); -- failure !

Hmmm. No surprise this din't work. Each time you specify the connect string, a 
connection is opened, the statement executed, and then the connection is 
closed -- i.e. each of the invocations of dblink_exec above stands alone. Are 
you suggesting a warning only on something like:
   SELECT dblink_exec('dbname=regression_slave', 'BEGIN');
? Seems like maybe a warning in the documentation would be enough. Any other 
opinions out there?

What occurs to me though, is that this is one of those "clients affected by 
the autocommit setting" situations. (...goes off and tries it out...) Sure 
enough. If you have autocommit set to off, you can do:
   SELECT dblink_exec('dbname=regression_slave',
             'INSERT INTO foo VALUES(12,''m'',''{"a12","b12","c12"}'');');
all day and never get it to succeed.

Given the above, should dblink_exec(CONNSTR, SQL) always wrap SQL in an 
explicit transaction? Any thoughts on this?

Joe



Sat, 12 Oct 2002 23:37:18 -0700
Joe Conway <mail@joeconway.com>
Re: Transactions through dblink_exec()
Mike Mascari wrote:
> How can dblink() possibly be used safely for non-readonly transactions 
> without a full implementation of a two-phase commit protocol? What 
> happens when the remote server issues the COMMIT and then the local 
> server crashes?
> 

It can't be used safely if you're trying to ensure a distributed transaction 
either fails or commits. At least I can't think of a way without two-phase 
commits implemented.

But depending on your scenario, just being sure that the remote transaction 
fails or succeeds as a unit may be all you care about.

Joe



Sat, 12 Oct 2002 23:44:18 -0700
Joe Conway <mail@joeconway.com>
Re: Transactions through dblink_exec()
On Sat, 12 Oct 2002 23:37:18 -0700
Joe Conway <mail@joeconway.com> wrote:

> Masaru Sugawara wrote:
> > I'm hoping that dblink_exec() returns something like warning if those
> > who intend to do transactions make a declaration of
> > blink_exec('dbname=some', 'begin') by mistake.
> > 
> > for example 
> >   WARNING :You should declare dblink_exec('dbname=some', 'BEGIN; some queries;
> > COMMIT/ROLLBACK/END;') or use dblink_exec('BEGIN/COMMIT/ROLLBACK/END')
> > around dblink_exec('some queries')s. If not, your transactions won't work.
> > 
> {...snip...]
> > 
> > -- case 3. -- 
> >   SELECT dblink_exec('dbname=regression_slave', 'BEGIN'); 
> >   SELECT dblink_exec('dbname=regression_slave',
> >                 'INSERT INTO foo VALUES(12,''m'',''{"a12","b12","c12"}'');');
> >   SELECT dblink_exec('dbname=regression_slave', 'ROLLBACK'); -- failure !
> 
> Hmmm. No surprise this din't work. Each time you specify the connect string, a 
> connection is opened, the statement executed, and then the connection is 
> closed -- i.e. each of the invocations of dblink_exec above stands alone. Are 
> you suggesting a warning only on something like:
>    SELECT dblink_exec('dbname=regression_slave', 'BEGIN');

Yes.


> ? Seems like maybe a warning in the documentation would be enough. 

Yes, certainly.  I came to think a warning in the doc is better than in the
command line because that is not a bug.


>Any other opinions out there?
> 
> What occurs to me though, is that this is one of those "clients affected by 
> the autocommit setting" situations. (...goes off and tries it out...) Sure 
> enough. If you have autocommit set to off, you can do:
>    SELECT dblink_exec('dbname=regression_slave',
>              'INSERT INTO foo VALUES(12,''m'',''{"a12","b12","c12"}'');');
> all day and never get it to succeed.

I didn't think of a situation of autocommit = off. As for me in some
transactions like the following, I haven't deeply worried about behaviors of 
dblink_exec(CONNSTR, 'BEGIN') because I would like to use dblink_connect() .
However, I'm not sure whether the following is perfectly safe against every
accident or not . 

BEGIN;
  SELECT dblink_connect('dbname=regression_slave');
  SELECT dblink_exec('BEGIN');
  SELECT dblink_exec('INSERT INTO foo VALUES(12, ''m'', ''{"a12","b12","c12"}'');');
  INSERT INTO foo VALUES(12, 'm', '{"a12","b12","c12"}');
  SELECT dblink_exec('END');
  SELECT dblink_disconnect();
END;

or 

CREATE OR REPLACE FUNCTION fn_mirror() RETURNS text AS '
DECLARE
  ret text;
BEGIN
  PERFORM dblink_connect(''dbname=regression_slave'');
  PERFORM dblink_exec(''BEGIN'');
  -- PERFORM dblink_exec(
  --      ''INSERT INTO foo VALUES(12, ''''m'''', ''''{"a12","b12","c12"}'''');'');
  SELECT INTO ret * FROM dblink_exec(
          ''INSERT INTO foo VALUES(12, ''''m'''', ''''{"a12","b12","c12"}'''');'');
  RAISE NOTICE ''slave : %'', ret;
  INSERT INTO foo VALUES(12, ''m'', ''{"a12","b12","c12"}'');
  PERFORM dblink_exec(''END'');
  PERFORM dblink_disconnect();
  RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

SELECT fn_mirror();


> 
> Given the above, should dblink_exec(CONNSTR, SQL) always wrap SQL in an 
> explicit transaction? Any thoughts on this?
> 
> Joe
> 
> 
> 


Regards,
Masaru Sugawara



Sun, 13 Oct 2002 19:07:41 +0900
Masaru Sugawara <rk73@sea.plala.or.jp>
Re: Transactions through dblink_exec()