Tom Lane wrote:
OK, easy enough. Patch attached.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.
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 *
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
