Re: Transactions through dblink_exec()

On Sat, 12 Oct 2002 23:37:18 -0700
Joe Conway <mail(at)joeconway(dot)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