Re: Transactions through dblink_exec() - Mailing list pgsql-hackers

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
fooVALUES(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(
''INSERTINTO foo VALUES(12, ''''m'''', ''''{"a12","b12","c12"}'''');''); RAISE NOTICE ''slave : %'', ret; INSERT INTO
fooVALUES(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