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.