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 = "" 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 = "" 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.
