REVIEW: alter extension upgrade (patch v3)

Started by Anssi Kääriäinenalmost 15 years ago2 messages

anssi.kaariainen@thl.fi

almost 15 years ago

2 attachment(s)

This review is mostly about trying to break stuff by using brute force.
I am not skilled enough in C to do a code level review.

The patch applies cleanly after applying the extensions support for
pg_dump patch. The patch is in context format.

There is one make check error:
test rules ... FAILED

The regression.diffs file is attached.

When building the contrib modules, make installcheck has one failure:
test dblink ... FAILED

The regression.diffs for this is attached as contrib_regression.diffs

I don't know too well how the regression tests are supposed to be run,
so error on my part is very much possible.

There is no upgrade rule for adminpack. If I am not mistaken, this could
be defined as
upgrade_from_all = '.* => adminpack.sql'
as the adminpack.sql only contains create or replace statements.

dblink has upgrade rule
upgrade_from_null = 'null => dblink.upgrade.sql'
however, there is not file dblink.upgrade.sql. Same for btree_gin and
possibly others.

For example the int_aggregate.upgrade.sql uses this form:
alter function @extschema@.int_array_enum(integer[]) set extension
int_aggregate;
while btree_gist has this (no @extschema@):
alter function gbt_text_compress(internal) set extension btree_gist;
I am not sure which one of them is correct. I think having extschema
there is what is wanted, and if so, this should also be mentioned in the
documentation.

In general, I think the upgrade rules and associated .upgrade.sql files
should be revisited. I did not have time to go through them all.

Might it be possible that there is a risk of attaching incompatible
versions of objects to an extension? Say, you have contrib module foo
which is loaded in 8.4 with \i. The module contains function bar, which
is redefined in 9.1. When you upgrade from null, you will have the 8.4's
version of function bar registered in the extension foo. That is,
upgrade from null does not upgrade the objects in an extension, it just
attaches them to the extension. As a result, when restoring from dump,
you get back 9.1's version.

What will happen if you have tables, indexes etc depending on the
objects in the extension? Is it possible to create rules to upgrade
these extensions easily?

As the operator used for matching the version number is ~, the rule ' =>
foo.upgrade.sql' will match any version. This is just as specified, but
if you have two upgrade rules: '9.1 => foo.upgrade.sql.1' and '9.1.1 =>
foo.upgrade.sql.2' the regexp for 9.1 will match also 9.1.1 . This could
be quite surprising. Should the operator be such that exact match is
required, that is ^ is inserted before the search string and $ after it?
Also, should the ALTER EXTENSION UPGRADE give a notice: 'using rule
upgrade_from_xxx'?

While trying to fool the upgrade engine, I noticed that if you use
absolute paths, you get the error:
ERROR: script path not allowed
DETAIL: Extension's script are expected in "/usr/local/pgsql/share".
Is this correct? Isn't the path "/usr/local/pgsql/share/contrib"

Is it intentional that paths are allowed in the upgrade script name? You
can use ../../foobar.upgrade.sql as the filename.

All in all, so far the feature has been relatively easy and intuitive to
use. My biggest concern is the upgrade_from_null, as specified, does not
actually upgrade the objects, just attach them to the extension.

I have no more time to test the patch. I hope this is helpful as is.

- Anssi

Attachments:

regression.diffstext/plain; name=regression.diffsDownload

*** /home/akaariai/postgres-serializable/postgres/src/test/regress/expected/rules.out	2011-02-02 12:22:29.000000000 +0200
--- /home/akaariai/postgres-serializable/postgres/src/test/regress/results/rules.out	2011-02-02 12:54:50.000000000 +0200
***************
*** 1279,1285 ****
            viewname           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              definition                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  -----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   iexit                       | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
!  pg_available_extensions     | SELECT e.name, e.version, e.relocatable, e.comment, e.installed FROM pg_extensions() e(name, version, relocatable, comment, installed);
   pg_cursors                  | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
   pg_group                    | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
   pg_indexes                  | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
--- 1279,1285 ----
            viewname           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              definition                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  -----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   iexit                       | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
!  pg_available_extensions     | SELECT n.nspname AS schema, e.name, x.extversion AS installed, e.version, e.relocatable, e.comment FROM ((pg_available_extensions() e(name, version, relocatable, comment) LEFT JOIN pg_extension x ON ((e.name = x.extname))) LEFT JOIN pg_namespace n ON ((n.oid = x.extnamespace)));
   pg_cursors                  | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
   pg_group                    | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
   pg_indexes                  | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));

======================================================================

contrib_regression.diffstext/plain; name=contrib_regression.diffsDownload

*** /home/akaariai/postgres-serializable/postgres/contrib/dblink/expected/dblink.out	2011-02-02 12:22:29.000000000 +0200
--- /home/akaariai/postgres-serializable/postgres/contrib/dblink/results/dblink.out	2011-02-02 13:07:51.000000000 +0200
***************
*** 102,112 ****
  SELECT *
  FROM dblink('dbname=contrib_regression','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
!  a | b |     c      
! ---+---+------------
!  8 | i | {a8,b8,c8}
!  9 | j | {a9,b9,c9}
! (2 rows)
  
  -- should generate "connection not available" error
  SELECT *
--- 102,111 ----
  SELECT *
  FROM dblink('dbname=contrib_regression','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  -- should generate "connection not available" error
  SELECT *
***************
*** 115,248 ****
  ERROR:  connection not available
  -- create a persistent connection
  SELECT dblink_connect('dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  -- use the persistent connection
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
!  a | b |     c      
! ---+---+------------
!  8 | i | {a8,b8,c8}
!  9 | j | {a9,b9,c9}
! (2 rows)
! 
  -- open a cursor with bad SQL and fail_on_error set to false
  SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE:  relation "foobar" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not open cursor.
!  dblink_open 
! -------------
!  ERROR
! (1 row)
! 
  -- reset remote transaction state
  SELECT dblink_exec('ABORT');
!  dblink_exec 
! -------------
!  ROLLBACK
! (1 row)
! 
  -- open a cursor
  SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
!  dblink_open 
! -------------
!  OK
! (1 row)
! 
  -- close the cursor
  SELECT dblink_close('rmt_foo_cursor',false);
!  dblink_close 
! --------------
!  OK
! (1 row)
! 
  -- open the cursor again
  SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
!  dblink_open 
! -------------
!  OK
! (1 row)
! 
  -- fetch some data
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
!  a | b |     c      
! ---+---+------------
!  0 | a | {a0,b0,c0}
!  1 | b | {a1,b1,c1}
!  2 | c | {a2,b2,c2}
!  3 | d | {a3,b3,c3}
! (4 rows)
! 
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
!  a | b |     c      
! ---+---+------------
!  4 | e | {a4,b4,c4}
!  5 | f | {a5,b5,c5}
!  6 | g | {a6,b6,c6}
!  7 | h | {a7,b7,c7}
! (4 rows)
! 
  -- this one only finds two rows left
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
!  a | b |     c      
! ---+---+------------
!  8 | i | {a8,b8,c8}
!  9 | j | {a9,b9,c9}
! (2 rows)
! 
  -- intentionally botch a fetch
  SELECT *
  FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE:  cursor "rmt_foobar_cursor" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not fetch from cursor.
!  a | b | c 
! ---+---+---
! (0 rows)
! 
  -- reset remote transaction state
  SELECT dblink_exec('ABORT');
!  dblink_exec 
! -------------
!  ROLLBACK
! (1 row)
! 
  -- close the wrong cursor
  SELECT dblink_close('rmt_foobar_cursor',false);
! NOTICE:  cursor "rmt_foobar_cursor" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not close cursor.
!  dblink_close 
! --------------
!  ERROR
! (1 row)
! 
  -- should generate 'cursor "rmt_foo_cursor" not found' error
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  cursor "rmt_foo_cursor" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not fetch from cursor.
  -- this time, 'cursor "rmt_foo_cursor" not found' as a notice
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE:  cursor "rmt_foo_cursor" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not fetch from cursor.
!  a | b | c 
! ---+---+---
! (0 rows)
! 
  -- close the persistent connection
  SELECT dblink_disconnect();
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  -- should generate "connection not available" error
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
--- 114,176 ----
  ERROR:  connection not available
  -- create a persistent connection
  SELECT dblink_connect('dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  -- use the persistent connection
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
! ERROR:  connection not available
  -- open a cursor with bad SQL and fail_on_error set to false
  SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
! ERROR:  connection not available
  -- reset remote transaction state
  SELECT dblink_exec('ABORT');
! ERROR:  connection not available
  -- open a cursor
  SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
! ERROR:  connection not available
  -- close the cursor
  SELECT dblink_close('rmt_foo_cursor',false);
! ERROR:  connection not available
  -- open the cursor again
  SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
! ERROR:  connection not available
  -- fetch some data
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection not available
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection not available
  -- this one only finds two rows left
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection not available
  -- intentionally botch a fetch
  SELECT *
  FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! ERROR:  connection not available
  -- reset remote transaction state
  SELECT dblink_exec('ABORT');
! ERROR:  connection not available
  -- close the wrong cursor
  SELECT dblink_close('rmt_foobar_cursor',false);
! ERROR:  connection not available
  -- should generate 'cursor "rmt_foo_cursor" not found' error
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection not available
  -- this time, 'cursor "rmt_foo_cursor" not found' as a notice
  SELECT *
  FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
! ERROR:  connection not available
  -- close the persistent connection
  SELECT dblink_disconnect();
! ERROR:  connection not available
  -- should generate "connection not available" error
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
***************
*** 251,351 ****
  -- 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=contrib_regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
!  substr 
! --------
!  INSERT
! (1 row)
  
  -- create a persistent connection
  SELECT dblink_connect('dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  -- 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
! (1 row)
! 
  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]);
!  a  | b |       c       
! ----+---+---------------
!   0 | a | {a0,b0,c0}
!   1 | b | {a1,b1,c1}
!   2 | c | {a2,b2,c2}
!   3 | d | {a3,b3,c3}
!   4 | e | {a4,b4,c4}
!   5 | f | {a5,b5,c5}
!   6 | g | {a6,b6,c6}
!   7 | h | {a7,b7,c7}
!   8 | i | {a8,b8,c8}
!   9 | j | {a9,b9,c9}
!  10 | k | {a10,b10,c10}
!  11 | l | {a11,b11,c11}
! (12 rows)
! 
  -- bad remote select
  SELECT *
  FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! NOTICE:  relation "foobar" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not execute query.
!  a | b | c 
! ---+---+---
! (0 rows)
! 
  -- change some data
  SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
!  dblink_exec 
! -------------
!  UPDATE 1
! (1 row)
! 
  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
!  a  | b |       c       
! ----+---+---------------
!  11 | l | {a11,b99,c11}
! (1 row)
! 
  -- botch a change to some other data
  SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! NOTICE:  relation "foobar" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not execute command.
!  dblink_exec 
! -------------
!  ERROR
! (1 row)
! 
  -- delete some data
  SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');
!  dblink_exec 
! -------------
!  DELETE 1
! (1 row)
! 
  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
!  a | b | c 
! ---+---+---
! (0 rows)
! 
  -- close the persistent connection
  SELECT dblink_disconnect();
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  --
  -- tests for the new named persistent connection syntax
  --
--- 179,230 ----
  -- 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=contrib_regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  -- create a persistent connection
  SELECT dblink_connect('dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  -- 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);
! ERROR:  connection not available
  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]);
! ERROR:  connection not available
  -- bad remote select
  SELECT *
  FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
! ERROR:  connection not available
  -- change some data
  SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
! ERROR:  connection not available
  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
! ERROR:  connection not available
  -- botch a change to some other data
  SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
! ERROR:  connection not available
  -- delete some data
  SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');
! ERROR:  connection not available
  -- let's see it
  SELECT *
  FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
! ERROR:  connection not available
  -- close the persistent connection
  SELECT dblink_disconnect();
! ERROR:  connection not available
  --
  -- tests for the new named persistent connection syntax
  --
***************
*** 358,581 ****
  
  -- create a named persistent connection
  SELECT dblink_connect('myconn','dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  -- use the named persistent connection
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
!  a  | b |       c       
! ----+---+---------------
!   8 | i | {a8,b8,c8}
!   9 | j | {a9,b9,c9}
!  10 | k | {a10,b10,c10}
! (3 rows)
  
  -- use the named persistent connection, but get it wrong
  SELECT *
  FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
  WHERE t.a > 7;
! NOTICE:  relation "foobar" does not exist
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not execute query.
!  a | b | c 
! ---+---+---
! (0 rows)
  
  -- create a second named persistent connection
  -- should error with "duplicate connection name"
  SELECT dblink_connect('myconn','dbname=contrib_regression');
! ERROR:  duplicate connection name
  -- create a second named persistent connection with a new name
  SELECT dblink_connect('myconn2','dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  -- use the second named persistent connection
  SELECT *
  FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
!  a  | b |       c       
! ----+---+---------------
!   8 | i | {a8,b8,c8}
!   9 | j | {a9,b9,c9}
!  10 | k | {a10,b10,c10}
! (3 rows)
  
  -- close the second named persistent connection
  SELECT dblink_disconnect('myconn2');
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  -- open a cursor incorrectly
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! NOTICE:  relation "foobar" does not exist
! CONTEXT:  Error occurred on dblink connection named "myconn": could not open cursor.
!  dblink_open 
! -------------
!  ERROR
! (1 row)
! 
  -- reset remote transaction state
  SELECT dblink_exec('myconn','ABORT');
!  dblink_exec 
! -------------
!  ROLLBACK
! (1 row)
  
  -- test opening cursor in a transaction
  SELECT dblink_exec('myconn','BEGIN');
!  dblink_exec 
! -------------
!  BEGIN
! (1 row)
  
  -- an open transaction will prevent dblink_open() from opening its own
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
!  dblink_open 
! -------------
!  OK
! (1 row)
! 
  -- this should not commit the transaction because the client opened it
  SELECT dblink_close('myconn','rmt_foo_cursor');
!  dblink_close 
! --------------
!  OK
! (1 row)
! 
  -- this should succeed because we have an open transaction
  SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
!   dblink_exec   
! ----------------
!  DECLARE CURSOR
! (1 row)
  
  -- commit remote transaction
  SELECT dblink_exec('myconn','COMMIT');
!  dblink_exec 
! -------------
!  COMMIT
! (1 row)
  
  -- test automatic transactions for multiple cursor opens
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
!  dblink_open 
! -------------
!  OK
! (1 row)
! 
  -- the second cursor
  SELECT dblink_open('myconn','rmt_foo_cursor2','SELECT * FROM foo');
!  dblink_open 
! -------------
!  OK
! (1 row)
! 
  -- this should not commit the transaction
  SELECT dblink_close('myconn','rmt_foo_cursor2');
!  dblink_close 
! --------------
!  OK
! (1 row)
! 
  -- this should succeed because we have an open transaction
  SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
!   dblink_exec   
! ----------------
!  DECLARE CURSOR
! (1 row)
  
  -- this should commit the transaction
  SELECT dblink_close('myconn','rmt_foo_cursor');
!  dblink_close 
! --------------
!  OK
! (1 row)
! 
  -- this should fail because there is no open transaction
  SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR:  DECLARE CURSOR can only be used in transaction blocks
! CONTEXT:  Error occurred on dblink connection named "unnamed": could not execute command.
  -- reset remote transaction state
  SELECT dblink_exec('myconn','ABORT');
!  dblink_exec 
! -------------
!  ROLLBACK
! (1 row)
  
  -- open a cursor
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
!  dblink_open 
! -------------
!  OK
! (1 row)
! 
  -- fetch some data
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
!  a | b |     c      
! ---+---+------------
!  0 | a | {a0,b0,c0}
!  1 | b | {a1,b1,c1}
!  2 | c | {a2,b2,c2}
!  3 | d | {a3,b3,c3}
! (4 rows)
! 
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
!  a | b |     c      
! ---+---+------------
!  4 | e | {a4,b4,c4}
!  5 | f | {a5,b5,c5}
!  6 | g | {a6,b6,c6}
!  7 | h | {a7,b7,c7}
! (4 rows)
! 
  -- this one only finds three rows left
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
!  a  | b |       c       
! ----+---+---------------
!   8 | i | {a8,b8,c8}
!   9 | j | {a9,b9,c9}
!  10 | k | {a10,b10,c10}
! (3 rows)
! 
  -- fetch some data incorrectly
  SELECT *
  FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! NOTICE:  cursor "rmt_foobar_cursor" does not exist
! CONTEXT:  Error occurred on dblink connection named "myconn": could not fetch from cursor.
!  a | b | c 
! ---+---+---
! (0 rows)
! 
  -- reset remote transaction state
  SELECT dblink_exec('myconn','ABORT');
!  dblink_exec 
! -------------
!  ROLLBACK
! (1 row)
  
  -- should generate 'cursor "rmt_foo_cursor" not found' error
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  cursor "rmt_foo_cursor" does not exist
! CONTEXT:  Error occurred on dblink connection named "myconn": could not fetch from cursor.
  -- close the named persistent connection
  SELECT dblink_disconnect('myconn');
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  -- should generate "missing "=" after "myconn" in connection info string" error
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
--- 237,372 ----
  
  -- create a named persistent connection
  SELECT dblink_connect('myconn','dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  -- use the named persistent connection
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- use the named persistent connection, but get it wrong
  SELECT *
  FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
  WHERE t.a > 7;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- create a second named persistent connection
  -- should error with "duplicate connection name"
  SELECT dblink_connect('myconn','dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
! 
  -- create a second named persistent connection with a new name
  SELECT dblink_connect('myconn2','dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  -- use the second named persistent connection
  SELECT *
  FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE t.a > 7;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn2" in connection info string
  
  -- close the second named persistent connection
  SELECT dblink_disconnect('myconn2');
! ERROR:  connection "myconn2" not available
  -- open a cursor incorrectly
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
! ERROR:  connection "myconn" not available
  -- reset remote transaction state
  SELECT dblink_exec('myconn','ABORT');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- test opening cursor in a transaction
  SELECT dblink_exec('myconn','BEGIN');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- an open transaction will prevent dblink_open() from opening its own
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
! ERROR:  connection "myconn" not available
  -- this should not commit the transaction because the client opened it
  SELECT dblink_close('myconn','rmt_foo_cursor');
! ERROR:  connection "myconn" not available
  -- this should succeed because we have an open transaction
  SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- commit remote transaction
  SELECT dblink_exec('myconn','COMMIT');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- test automatic transactions for multiple cursor opens
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
! ERROR:  connection "myconn" not available
  -- the second cursor
  SELECT dblink_open('myconn','rmt_foo_cursor2','SELECT * FROM foo');
! ERROR:  connection "myconn" not available
  -- this should not commit the transaction
  SELECT dblink_close('myconn','rmt_foo_cursor2');
! ERROR:  connection "myconn" not available
  -- this should succeed because we have an open transaction
  SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- this should commit the transaction
  SELECT dblink_close('myconn','rmt_foo_cursor');
! ERROR:  connection "myconn" not available
  -- this should fail because there is no open transaction
  SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
! 
  -- reset remote transaction state
  SELECT dblink_exec('myconn','ABORT');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- open a cursor
  SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
! ERROR:  connection "myconn" not available
  -- fetch some data
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection "myconn" not available
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection "myconn" not available
  -- this one only finds three rows left
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection "myconn" not available
  -- fetch some data incorrectly
  SELECT *
  FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
! ERROR:  connection "myconn" not available
  -- reset remote transaction state
  SELECT dblink_exec('myconn','ABORT');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- should generate 'cursor "rmt_foo_cursor" not found' error
  SELECT *
  FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
! ERROR:  connection "myconn" not available
  -- close the named persistent connection
  SELECT dblink_disconnect('myconn');
! ERROR:  connection "myconn" not available
  -- should generate "missing "=" after "myconn" in connection info string" error
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
***************
*** 585,703 ****
  
  -- create a named persistent connection
  SELECT dblink_connect('myconn','dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  -- put more data into our slave table, using named persistent connection syntax
  -- but truncate the actual return value so we can use diff to check for success
  SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
!  substr 
! --------
!  INSERT
! (1 row)
  
  -- let's see it
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
!  a  | b |       c       
! ----+---+---------------
!   0 | a | {a0,b0,c0}
!   1 | b | {a1,b1,c1}
!   2 | c | {a2,b2,c2}
!   3 | d | {a3,b3,c3}
!   4 | e | {a4,b4,c4}
!   5 | f | {a5,b5,c5}
!   6 | g | {a6,b6,c6}
!   7 | h | {a7,b7,c7}
!   8 | i | {a8,b8,c8}
!   9 | j | {a9,b9,c9}
!  10 | k | {a10,b10,c10}
!  11 | l | {a11,b11,c11}
! (12 rows)
  
  -- change some data
  SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
!  dblink_exec 
! -------------
!  UPDATE 1
! (1 row)
  
  -- let's see it
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
!  a  | b |       c       
! ----+---+---------------
!  11 | l | {a11,b99,c11}
! (1 row)
  
  -- delete some data
  SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
!  dblink_exec 
! -------------
!  DELETE 1
! (1 row)
  
  -- let's see it
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
!  a | b | c 
! ---+---+---
! (0 rows)
  
  -- close the named persistent connection
  SELECT dblink_disconnect('myconn');
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  -- close the named persistent connection again
  -- should get 'connection "myconn" not available' error
  SELECT dblink_disconnect('myconn');
  ERROR:  connection "myconn" not available
  -- test asynchronous queries
  SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  SELECT * from
   dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
!  t1 
! ----
!   1
! (1 row)
  
  SELECT dblink_connect('dtest2', 'dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  SELECT * from
   dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1;
!  t1 
! ----
!   1
! (1 row)
  
  SELECT dblink_connect('dtest3', 'dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  SELECT * from
   dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1;
!  t1 
! ----
!   1
! (1 row)
  
  CREATE TEMPORARY TABLE result AS
  (SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]))
--- 376,462 ----
  
  -- create a named persistent connection
  SELECT dblink_connect('myconn','dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  -- put more data into our slave table, using named persistent connection syntax
  -- but truncate the actual return value so we can use diff to check for success
  SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- let's see it
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- change some data
  SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- let's see it
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- delete some data
  SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- let's see it
  SELECT *
  FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
  WHERE a = 11;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "myconn" in connection info string
  
  -- close the named persistent connection
  SELECT dblink_disconnect('myconn');
! ERROR:  connection "myconn" not available
  -- close the named persistent connection again
  -- should get 'connection "myconn" not available' error
  SELECT dblink_disconnect('myconn');
  ERROR:  connection "myconn" not available
  -- test asynchronous queries
  SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  SELECT * from
   dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "dtest1" in connection info string
  
  SELECT dblink_connect('dtest2', 'dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  SELECT * from
   dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "dtest2" in connection info string
  
  SELECT dblink_connect('dtest3', 'dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  SELECT * from
   dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "dtest3" in connection info string
  
  CREATE TEMPORARY TABLE result AS
  (SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]))
***************
*** 706,711 ****
--- 465,473 ----
  UNION
  (SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[]))
  ORDER by f1;
+ ERROR:  could not establish connection
+ DETAIL:  missing "=" after "dtest1" in connection info string
+ 
  -- dblink_get_connections returns an array with elements in a machine-dependent
  -- ordering, so we must resort to unnesting and sorting for a stable result
  create function unnest(anyarray) returns setof anyelement
***************
*** 715,796 ****
  SELECT * FROM unnest(dblink_get_connections()) ORDER BY 1;
   unnest 
  --------
!  dtest1
!  dtest2
!  dtest3
! (3 rows)
  
  SELECT dblink_is_busy('dtest1');
!  dblink_is_busy 
! ----------------
!               0
! (1 row)
! 
  SELECT dblink_disconnect('dtest1');
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  SELECT dblink_disconnect('dtest2');
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  SELECT dblink_disconnect('dtest3');
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  SELECT * from result;
!  f1 | f2 |      f3       
! ----+----+---------------
!   0 | a  | {a0,b0,c0}
!   1 | b  | {a1,b1,c1}
!   2 | c  | {a2,b2,c2}
!   3 | d  | {a3,b3,c3}
!   4 | e  | {a4,b4,c4}
!   5 | f  | {a5,b5,c5}
!   6 | g  | {a6,b6,c6}
!   7 | h  | {a7,b7,c7}
!   8 | i  | {a8,b8,c8}
!   9 | j  | {a9,b9,c9}
!  10 | k  | {a10,b10,c10}
! (11 rows)
! 
  SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  SELECT * from
   dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
!  t1 
! ----
!   1
! (1 row)
  
  SELECT dblink_cancel_query('dtest1');
!  dblink_cancel_query 
! ---------------------
!  OK
! (1 row)
! 
  SELECT dblink_error_message('dtest1');
!  dblink_error_message 
! ----------------------
!  OK
! (1 row)
! 
  SELECT dblink_disconnect('dtest1');
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  -- test foreign data wrapper functionality
  CREATE USER dblink_regression_test;
  CREATE FOREIGN DATA WRAPPER postgresql;
--- 477,513 ----
  SELECT * FROM unnest(dblink_get_connections()) ORDER BY 1;
   unnest 
  --------
! (0 rows)
  
  SELECT dblink_is_busy('dtest1');
! ERROR:  connection "dtest1" not available
  SELECT dblink_disconnect('dtest1');
! ERROR:  connection "dtest1" not available
  SELECT dblink_disconnect('dtest2');
! ERROR:  connection "dtest2" not available
  SELECT dblink_disconnect('dtest3');
! ERROR:  connection "dtest3" not available
  SELECT * from result;
! ERROR:  relation "result" does not exist
! LINE 1: SELECT * from result;
!                       ^
  SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  SELECT * from
   dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
! ERROR:  could not establish connection
! DETAIL:  missing "=" after "dtest1" in connection info string
  
  SELECT dblink_cancel_query('dtest1');
! ERROR:  connection "dtest1" not available
  SELECT dblink_error_message('dtest1');
! ERROR:  connection "dtest1" not available
  SELECT dblink_disconnect('dtest1');
! ERROR:  connection "dtest1" not available
  -- test foreign data wrapper functionality
  CREATE USER dblink_regression_test;
  CREATE FOREIGN DATA WRAPPER postgresql;
***************
*** 806,832 ****
  DETAIL:  Non-superusers must provide a password in the connection string.
  -- should succeed
  SELECT dblink_connect_u('myconn', 'fdtest');
!  dblink_connect_u 
! ------------------
!  OK
! (1 row)
  
  SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
!  a  | b |       c       
! ----+---+---------------
!   0 | a | {a0,b0,c0}
!   1 | b | {a1,b1,c1}
!   2 | c | {a2,b2,c2}
!   3 | d | {a3,b3,c3}
!   4 | e | {a4,b4,c4}
!   5 | f | {a5,b5,c5}
!   6 | g | {a6,b6,c6}
!   7 | h | {a7,b7,c7}
!   8 | i | {a8,b8,c8}
!   9 | j | {a9,b9,c9}
!  10 | k | {a10,b10,c10}
! (11 rows)
! 
  \c - :ORIGINAL_USER
  REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
  REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test;
--- 523,536 ----
  DETAIL:  Non-superusers must provide a password in the connection string.
  -- should succeed
  SELECT dblink_connect_u('myconn', 'fdtest');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
! ERROR:  password is required
! DETAIL:  Non-superusers must provide a password in the connection string.
  \c - :ORIGINAL_USER
  REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
  REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test;
***************
*** 836,878 ****
  DROP FOREIGN DATA WRAPPER postgresql;
  -- test asynchronous notifications
  SELECT dblink_connect('dbname=contrib_regression');
!  dblink_connect 
! ----------------
!  OK
! (1 row)
  
  --should return listen
  SELECT dblink_exec('LISTEN regression');
!  dblink_exec 
! -------------
!  LISTEN
! (1 row)
! 
  --should return listen
  SELECT dblink_exec('LISTEN foobar');
!  dblink_exec 
! -------------
!  LISTEN
! (1 row)
! 
  SELECT dblink_exec('NOTIFY regression');
!  dblink_exec 
! -------------
!  NOTIFY
! (1 row)
! 
  SELECT dblink_exec('NOTIFY foobar');
!  dblink_exec 
! -------------
!  NOTIFY
! (1 row)
! 
  SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid()') as t(be_pid int)) AS is_self_notify, extra from dblink_get_notify();
   notify_name | is_self_notify | extra 
  -------------+----------------+-------
!  regression  | t              | 
!  foobar      | t              | 
! (2 rows)
  
  SELECT * from dblink_get_notify();
   notify_name | be_pid | extra 
--- 540,564 ----
  DROP FOREIGN DATA WRAPPER postgresql;
  -- test asynchronous notifications
  SELECT dblink_connect('dbname=contrib_regression');
! ERROR:  could not establish connection
! DETAIL:  could not connect to server: No such file or directory
! 	Is the server running locally and accepting
! 	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
  
  --should return listen
  SELECT dblink_exec('LISTEN regression');
! ERROR:  connection not available
  --should return listen
  SELECT dblink_exec('LISTEN foobar');
! ERROR:  connection not available
  SELECT dblink_exec('NOTIFY regression');
! ERROR:  connection not available
  SELECT dblink_exec('NOTIFY foobar');
! ERROR:  connection not available
  SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid()') as t(be_pid int)) AS is_self_notify, extra from dblink_get_notify();
   notify_name | is_self_notify | extra 
  -------------+----------------+-------
! (0 rows)
  
  SELECT * from dblink_get_notify();
   notify_name | be_pid | extra 
***************
*** 880,890 ****
  (0 rows)
  
  SELECT dblink_disconnect();
!  dblink_disconnect 
! -------------------
!  OK
! (1 row)
! 
  -- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
  CREATE TEMP TABLE test_dropped
  (
--- 566,572 ----
  (0 rows)
  
  SELECT dblink_disconnect();
! ERROR:  connection not available
  -- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
  CREATE TEMP TABLE test_dropped
  (

======================================================================

Dimitri Fontaine

dimitri@2ndQuadrant.fr

almost 15 years ago

In reply to: Anssi Kääriäinen (#1)

Re: REVIEW: alter extension upgrade (patch v3)

Hi,

Anssi Kääriäinen <anssi.kaariainen@thl.fi> writes:

There is one make check error:
test rules ... FAILED

I forgot to update the pg_available_extensions system view in the tests,
but failed to do so in the extension's patch too, where Itagaki fixed
it. Will fix if not beaten to it, in which case the fix for this very
patch will fall down of rebasing against master…

When building the contrib modules, make installcheck has one failure:
test dblink ... FAILED

Apparently your setup is not allowing dblink to connect…

There is no upgrade rule for adminpack. If I am not mistaken, this could be
defined as
upgrade_from_all = '.* => adminpack.sql'
as the adminpack.sql only contains create or replace statements.

Right. Sorry about missing it. In fact the regexp .* will not match
NULL, so you have to add a specific NULL case.

dblink has upgrade rule
upgrade_from_null = 'null => dblink.upgrade.sql'
however, there is not file dblink.upgrade.sql. Same for btree_gin and
possibly others.

Those must have been missed in my git adding, I'm quite sure I have them
on another computer than the one I'm using here, will fix later. Sorry
about that.

For example the int_aggregate.upgrade.sql uses this form:
alter function @extschema@.int_array_enum(integer[]) set extension
int_aggregate;
while btree_gist has this (no @extschema@):
alter function gbt_text_compress(internal) set extension btree_gist;
I am not sure which one of them is correct. I think having extschema there
is what is wanted, and if so, this should also be mentioned in the
documentation.

The @extschema@ is correct, sorry about that too. I've been pushing to
finish all those upgrade scripts early enough :(

Here's what says the comments in the code (which I've been working on a
moon ago, with much less pressure and tiredness):

* At upgrade time, it's highly possible that the script will need
* to ALTER already existing objects, so we only offer support for
* the placeholder @extschema@.

So I think the code is ok but the upgrade scripts will need another
round of care.

In general, I think the upgrade rules and associated .upgrade.sql files
should be revisited. I did not have time to go through them all.

Will do. Thanks for the heads up.

Might it be possible that there is a risk of attaching incompatible versions
of objects to an extension? Say, you have contrib module foo which is loaded
in 8.4 with \i. The module contains function bar, which is redefined in
9.1. When you upgrade from null, you will have the 8.4's version of function
bar registered in the extension foo. That is, upgrade from null does not
upgrade the objects in an extension, it just attaches them to the
extension. As a result, when restoring from dump, you get back 9.1's
version.

What will happen if you have tables, indexes etc depending on the objects in
the extension? Is it possible to create rules to upgrade these extensions
easily?

Let's not mix the mechanism and how we use it. What's in the patch is a
way for the authors to provide for upgrade scripts. If 9.0 and 9.1
versions of the extension are different there's no reason for them to
provide the same upgrade from null script.

See my lo example on a previous mail to Itagaki.

As the operator used for matching the version number is ~, the rule ' =>
foo.upgrade.sql' will match any version. This is just as specified, but if
you have two upgrade rules: '9.1 => foo.upgrade.sql.1' and '9.1.1 =>
foo.upgrade.sql.2' the regexp for 9.1 will match also 9.1.1 . This could be
quite surprising. Should the operator be such that exact match is required,
that is ^ is inserted before the search string and $ after it? Also, should
the ALTER EXTENSION UPGRADE give a notice: 'using rule upgrade_from_xxx'?

Yeah, anchored matches might be a better choice here. Will wait for
some more comments and do just that baring objections.

As far as the notice is concerned, though, you have the information at
the DEBUG1 level, I'm not sure about getting a notice message. That's
useful for authors debugging their control files, not for users…

While trying to fool the upgrade engine, I noticed that if you use absolute
paths, you get the error:
ERROR: script path not allowed
DETAIL: Extension's script are expected in "/usr/local/pgsql/share".
Is this correct? Isn't the path "/usr/local/pgsql/share/contrib"

Is it intentional that paths are allowed in the upgrade script name? You can
use ../../foobar.upgrade.sql as the filename.

Itagaki raised some concerns about the path management too, so I will
follow what comes out of this in the upgrade patch too.

All in all, so far the feature has been relatively easy and intuitive to
use. My biggest concern is the upgrade_from_null, as specified, does not
actually upgrade the objects, just attach them to the extension.

I have no more time to test the patch. I hope this is helpful as is.

Very much so, thanks you!

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support