pg_dumping extensions having sequences with 9.6beta3

Hi all,

I am currently playing with extensions. And I found a strange behaviour change with 9.6beta2 and 3 when pg_dumping a database with an extension having sequences. This looks like a bug, ... unless I did something wrong.

Here is a test case (a simple linux shell script, that can be easily customized to reproduce).

# pg_dump issue in postgres 9.6beta2 when dumping sequences linked to extensions
#
export PGBIN="/usr/local/pg96beta3/bin"
#export PGBIN="/usr/local/pg952/bin"
export EXTDIR="/tmp"
export PGDIR="/usr/local/pg96beta3/share/postgresql/extension"
#export PGDIR="/usr/local/pg952/share/postgresql/extension"
export PGPORT=5496
#export PGPORT=5495
export PGDATABASE='postgres'

echo "##################################################################################################################"
echo " "
echo "psql: prepare the initial environment: 1 schema + 2 tables with 1 serial column in each"
echo "---------------------------------------------------------------------------------------"
$PGBIN/psql -a <<*END*
select version();
-- cleanup
DROP EXTENSION IF EXISTS myextension;
DROP SCHEMA IF EXISTS myextension CASCADE;
-- create
CREATE SCHEMA myextension;
CREATE TABLE myextension.t1 (c1 SERIAL);
CREATE TABLE myextension.t2 (c1 SERIAL);
*END*

echo "create first files for extension management"
echo "-------------------------------------------"
cat >$EXTDIR/myextension.control <<*END*
default_version        = '1'
comment                = 'test'
directory            = '$EXTDIR'
superuser            = true
schema                = 'myextension'
relocatable            = false
*END*
sudo ln -s $EXTDIR/myextension.control $PGDIR/myextension.control

cat >$EXTDIR/myextension--unpackaged--1.sql <<*END*
-- for t1, the table and the sequence is added to the extension
ALTER EXTENSION myextension ADD TABLE myextension.t1;
ALTER EXTENSION myextension ADD SEQUENCE myextension.t1_c1_seq;
-- for t2, the associated sequence is not added to the extension for now
ALTER EXTENSION myextension ADD TABLE myextension.t2;
-- create a new t3 table
CREATE TABLE t3 (c1 SERIAL);
*END*

echo "psql: create the extension from unpackaged"
echo "------------------------------------------"
$PGBIN/psql -a <<*END*
-- create
CREATE EXTENSION myextension FROM unpackaged;
-- check
\dx
SELECT classid, c1.relname, objid, c2.relname, c2.relkind, refclassid, r.relname, refobjid
  FROM pg_depend, pg_class c1, pg_class r, pg_class c2
  WHERE deptype = 'e'
    AND classid = c1.oid AND refclassid = r.oid AND objid = c2.oid
    AND c1.relname = 'pg_class';
*END*

echo " "
echo "So we now have 3 tables having a serial column:"
echo " - t1 explicitely added to the extension, with its sequence"
echo " - t2 explicitely added to the extension, but without its sequence"
echo " - t3 directly created inside the extensione"
echo " "

echo "sequences dumped by pg_dump (pg_dump |grep 'CREATE SEQUENCE')"
echo "---------------------------"
$PGBIN/pg_dump |grep 'CREATE SEQUENCE'

echo " "
echo "=> as expected, with latest minor versions of postgres 9.1 to 9.5, the sequences associated to the t1.c1 and t1.c3 columns are not dumped,"
echo "   while the sequence associated to t2.c1 is dumped."
echo "=> with 9.6beta3 (as with beta2), the 3 sequences are dumped."
echo " "

echo "cleanup"
echo "-------"
$PGBIN/psql <<*END*
DROP EXTENSION IF EXISTS myextension;
DROP SCHEMA IF EXISTS myextension CASCADE;
*END*

sudo rm $PGDIR/myextension.control
rm $EXTDIR/myextension*

And its output result:

##################################################################################################################
 
psql: prepare the initial environment: 1 schema + 2 tables with 1 serial column in each
---------------------------------------------------------------------------------------
select version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6beta3 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit
(1 row)

-- cleanup
DROP EXTENSION IF EXISTS myextension;
NOTICE:  extension "myextension" does not exist, skipping
DROP EXTENSION
DROP SCHEMA IF EXISTS myextension CASCADE;
NOTICE:  schema "myextension" does not exist, skipping
DROP SCHEMA
-- create
CREATE SCHEMA myextension;
CREATE SCHEMA
CREATE TABLE myextension.t1 (c1 SERIAL);
CREATE TABLE
CREATE TABLE myextension.t2 (c1 SERIAL);
CREATE TABLE
create first files for extension management
-------------------------------------------
psql: create the extension from unpackaged
------------------------------------------
-- create
CREATE EXTENSION myextension FROM unpackaged;
CREATE EXTENSION
-- check
\dx
                    List of installed extensions
    Name     | Version |   Schema    |         Description         
-------------+---------+-------------+------------------------------
 myextension | 1       | myextension | test
 plpgsql     | 1.0     | pg_catalog  | PL/pgSQL procedural language
(2 rows)

SELECT classid, c1.relname, objid, c2.relname, c2.relkind, refclassid, r.relname, refobjid
  FROM pg_depend, pg_class c1, pg_class r, pg_class c2
  WHERE deptype = 'e'
    AND classid = c1.oid AND refclassid = r.oid AND objid = c2.oid
    AND c1.relname = 'pg_class';
 classid | relname  | objid |  relname  | relkind | refclassid |   relname    | refobjid
---------+----------+-------+-----------+---------+------------+--------------+----------
    1259 | pg_class | 32216 | t1        | r       |       3079 | pg_extension |    32226
    1259 | pg_class | 32214 | t1_c1_seq | S       |       3079 | pg_extension |    32226
    1259 | pg_class | 32222 | t2        | r       |       3079 | pg_extension |    32226
    1259 | pg_class | 32227 | t3_c1_seq | S       |       3079 | pg_extension |    32226
    1259 | pg_class | 32229 | t3        | r       |       3079 | pg_extension |    32226
(5 rows)

 
So we now have 3 tables having a serial column:
 - t1 explicitely added to the extension, with its sequence
 - t2 explicitely added to the extension, but without its sequence
 - t3 directly created inside the extensione
 
sequences dumped by pg_dump (pg_dump |grep 'CREATE SEQUENCE')
---------------------------
CREATE SEQUENCE t1_c1_seq
CREATE SEQUENCE t2_c1_seq
CREATE SEQUENCE t3_c1_seq
 
=> as expected, with latest minor versions of postgres 9.1 to 9.5, the sequences associated to the t1.c1 and t1.c3 columns are not dumped,
   while the sequence associated to t2.c1 is dumped.
=> with 9.6beta3 (as with beta2), the 3 sequences are dumped.
 
cleanup
-------
DROP EXTENSION
DROP SCHEMA

I will be off during the 2 coming weeks. So I will only see any reply to this thread ... soon.

Best regards.

Philippe Beaudoin.