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...]
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:
-- 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 !
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
---------------------------(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
