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 <[EMAIL PROTECTED]> 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 !
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
