Re: autocommit vs TRUNCATE et al - Mailing list pgsql-hackers

Tom Lane wrote:
> In the meantime, to tell you the truth, the cleanest way to handle the
> dblink regression test would be to make it circularly connect to
> database "regression".  I know this seems cheesy, but as long as the
> software under test doesn't know that it's a connection-to-self, seems
> like the test is perfectly good.  And it's surely easier to manage that
> way.

OK, easy enough. Patch attached.

I also added "SET autocommit TO ''on'';" to the beginning of each dblink_exec
input statement because the "SET autocommit TO 'on';" at the top of the script
won't help for the connected database.

Joe
Index: contrib/dblink/expected/dblink.out
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/expected/dblink.out,v
retrieving revision 1.5
diff -c -r1.5 dblink.out
*** contrib/dblink/expected/dblink.out    21 Oct 2002 01:42:13 -0000    1.5
--- contrib/dblink/expected/dblink.out    22 Oct 2002 05:19:40 -0000
***************
*** 1,31 ****
  --
! -- First, create a slave database and define the functions and test data
  -- therein.
  --
- -- This initial hackery is to allow successive runs without failures.
- --
- -- Adjust this setting to control where the objects get created.
- SET search_path = public;
- CREATE OR REPLACE FUNCTION conditional_drop()
- RETURNS text AS '
- DECLARE
-     dbname    text;
- BEGIN
-     SELECT INTO dbname datname FROM pg_database WHERE datname = ''regression_slave'';
-     IF FOUND THEN
-         DROP DATABASE regression_slave;
-     END IF;
-     RETURN ''OK'';
- END;
- ' LANGUAGE 'plpgsql';
- SELECT conditional_drop();
-  conditional_drop
- ------------------
-  OK
- (1 row)
-
- CREATE DATABASE regression_slave;
- \connect regression_slave
  -- Turn off echoing so that expected file does not depend on
  -- contents of dblink.sql.
  \set ECHO none
--- 1,9 ----
+ -- Adjust this setting to control where the objects get created.
+ SET search_path = public;
  --
! -- Define the functions and test data
  -- therein.
  --
  -- Turn off echoing so that expected file does not depend on
  -- contents of dblink.sql.
  \set ECHO none
***************
*** 81,96 ****
   DELETE FROM foo WHERE f1 = '0' AND f2 = 'a'
  (1 row)

- --
- -- Connect back to the regression database and define the functions.
- -- Turn off echoing so that expected file does not depend on
- -- contents of dblink.sql.
- --
- \connect regression
- \set ECHO none
  -- regular old dblink
  SELECT *
! FROM dblink('dbname=regression_slave','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
   a | b |     c
  ---+---+------------
--- 59,67 ----
   DELETE FROM foo WHERE f1 = '0' AND f2 = 'a'
  (1 row)

  -- regular old dblink
  SELECT *
! FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
   a | b |     c
  ---+---+------------
***************
*** 104,110 ****
  WHERE t.a > 7;
  ERROR:  dblink: no connection available
  -- create a persistent connection
! SELECT dblink_connect('dbname=regression_slave');
   dblink_connect
  ----------------
   OK
--- 75,81 ----
  WHERE t.a > 7;
  ERROR:  dblink: no connection available
  -- create a persistent connection
! SELECT dblink_connect('dbname=regression');
   dblink_connect
  ----------------
   OK
***************
*** 182,195 ****
  ERROR:  dblink: no connection available
  -- put more data into our slave table, first using arbitrary connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('dbname=regression_slave','SET autocommit TO ''on'';INSERT INTO foo VALUES
(10,''k'',''{"a10","b10","c10"}'')'),1,6);
   substr
  --------
   INSERT
  (1 row)

  -- create a persistent connection
! SELECT dblink_connect('dbname=regression_slave');
   dblink_connect
  ----------------
   OK
--- 153,166 ----
  ERROR:  dblink: no connection available
  -- put more data into our slave table, first using arbitrary connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('dbname=regression','SET autocommit TO ''on'';INSERT INTO foo
VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
   substr
  --------
   INSERT
  (1 row)

  -- create a persistent connection
! SELECT dblink_connect('dbname=regression');
   dblink_connect
  ----------------
   OK
***************
*** 197,203 ****

  -- put more data into our slave table, using persistent connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('INSERT INTO foo VALUES (11,''l'',''{"a11","b11","c11"}'')'),1,6);
   substr
  --------
   INSERT
--- 168,174 ----

  -- put more data into our slave table, using persistent connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('SET autocommit TO ''on'';INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
   substr
  --------
   INSERT
***************
*** 223,229 ****
  (12 rows)

  -- change some data
! SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
   dblink_exec
  -------------
   UPDATE 1
--- 194,200 ----
  (12 rows)

  -- change some data
! SELECT dblink_exec('SET autocommit TO ''on'';UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
   dblink_exec
  -------------
   UPDATE 1
***************
*** 239,245 ****
  (1 row)

  -- delete some data
! SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');
   dblink_exec
  -------------
   DELETE 1
--- 210,216 ----
  (1 row)

  -- delete some data
! SELECT dblink_exec('SET autocommit TO ''on'';DELETE FROM foo WHERE f1 = 11');
   dblink_exec
  -------------
   DELETE 1
Index: contrib/dblink/sql/dblink.sql
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/sql/dblink.sql,v
retrieving revision 1.5
diff -c -r1.5 dblink.sql
*** contrib/dblink/sql/dblink.sql    21 Oct 2002 01:42:13 -0000    1.5
--- contrib/dblink/sql/dblink.sql    22 Oct 2002 05:19:19 -0000
***************
*** 1,30 ****
- --
- -- First, create a slave database and define the functions and test data
- -- therein.
- --
- -- This initial hackery is to allow successive runs without failures.
- --
-
  -- Adjust this setting to control where the objects get created.
  SET search_path = public;

! CREATE OR REPLACE FUNCTION conditional_drop()
! RETURNS text AS '
! DECLARE
!     dbname    text;
! BEGIN
!     SELECT INTO dbname datname FROM pg_database WHERE datname = ''regression_slave'';
!     IF FOUND THEN
!         DROP DATABASE regression_slave;
!     END IF;
!     RETURN ''OK'';
! END;
! ' LANGUAGE 'plpgsql';
! SELECT conditional_drop();
!
! CREATE DATABASE regression_slave;
! \connect regression_slave
!
  -- Turn off echoing so that expected file does not depend on
  -- contents of dblink.sql.
  \set ECHO none
--- 1,10 ----
  -- Adjust this setting to control where the objects get created.
  SET search_path = public;

! --
! -- Define the functions and test data
! -- therein.
! --
  -- Turn off echoing so that expected file does not depend on
  -- contents of dblink.sql.
  \set ECHO none
***************
*** 64,83 ****
  -- build a delete statement based on a local tuple,
  SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');

- --
- -- Connect back to the regression database and define the functions.
- -- Turn off echoing so that expected file does not depend on
- -- contents of dblink.sql.
- --
- \connect regression
- \set ECHO none
- SET autocommit TO 'on';
- \i dblink.sql
- \set ECHO all
-
  -- regular old dblink
  SELECT *
! FROM dblink('dbname=regression_slave','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;

  -- should generate "no connection available" error
--- 44,52 ----
  -- build a delete statement based on a local tuple,
  SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');

  -- regular old dblink
  SELECT *
! FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;

  -- should generate "no connection available" error
***************
*** 86,92 ****
  WHERE t.a > 7;

  -- create a persistent connection
! SELECT dblink_connect('dbname=regression_slave');

  -- use the persistent connection
  SELECT *
--- 55,61 ----
  WHERE t.a > 7;

  -- create a persistent connection
! SELECT dblink_connect('dbname=regression');

  -- use the persistent connection
  SELECT *
***************
*** 124,144 ****

  -- put more data into our slave table, first using arbitrary connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('dbname=regression_slave','SET autocommit TO ''on'';INSERT INTO foo
VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);

  -- create a persistent connection
! SELECT dblink_connect('dbname=regression_slave');

  -- put more data into our slave table, using persistent connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);

  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]);

  -- change some data
! SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');

  -- let's see it
  SELECT *
--- 93,113 ----

  -- put more data into our slave table, first using arbitrary connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('dbname=regression','SET autocommit TO ''on'';INSERT INTO foo
VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);

  -- create a persistent connection
! SELECT dblink_connect('dbname=regression');

  -- put more data into our slave table, using persistent connection syntax
  -- but truncate the actual return value so we can use diff to check for success
! SELECT substr(dblink_exec('SET autocommit TO ''on'';INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);

  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]);

  -- change some data
! SELECT dblink_exec('SET autocommit TO ''on'';UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');

  -- let's see it
  SELECT *
***************
*** 146,152 ****
  WHERE a = 11;

  -- delete some data
! SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');

  -- let's see it
  SELECT *
--- 115,121 ----
  WHERE a = 11;

  -- delete some data
! SELECT dblink_exec('SET autocommit TO ''on'';DELETE FROM foo WHERE f1 = 11');

  -- let's see it
  SELECT *