BUG #6683: dropping a table with a serial column from an extension needs to explicitely drop the associated seq

The following bug has been logged on the website:

Bug reference:      6683
Logged by:          Philippe BEAUDOIN
Email address:      phb.emaj@free.fr
PostgreSQL version: 9.1.4
Operating system:   Linux
Description:=20=20=20=20=20=20=20=20

When a table having a seial column has been created by a CREATE EXTENSION,
and when this table is later dropped from the extension, the associated
sequence must be also explicitely dropped from the extension.=20

Otherwise, after the table is detached from the extension, neither the table
can be dropped, nor the extension (except using CASCADE clauses).

This unattended behaviour could be considered as a design choice. In this
case, this should be clearly documentated.

Here is a test case to easily reproduce this issue.
Just copy/paste and adjust initial parameters.

#!/bin/sh

export PGINST=3D/usr/local/pg913
export PGBIN=3D$PGINST/bin
export PGEXT=3D$PGINST/share/postgresql/extension

export PGHOST=3Dlocalhost
export PGPORT=3D5913
export PGUSER=3Dpostgres
export PGDATABASE=3Dphb

echo
"-----------------------------------------------------------------------"
echo " Issue: dropping a table with a serial column from an extension needs
"
echo "        to explicitely drop the associated sequence from the
extension"
echo
"-----------------------------------------------------------------------"
echo Connection parameters: $PGHOST - $PGPORT - $PGUSER - $PGDATABASE

# Build the extension control file
cat >$PGINST/share/postgresql/extension/phb.control <<EOF=20
default_version =3D '1.0.0'
schema =3D phb
EOF

# Create a test database
$PGBIN/psql template1 -ac "drop database phb;"
$PGBIN/psql template1 -ac "create database phb;"

# create the create extension script
cat >$PGEXT/phb--1.0.0.sql <<EOF=20
create table tbl1 (col1 serial not null primary key, col2 int);
select pg_catalog.pg_extension_config_dump('tbl1','');
EOF

echo "--> Create the extension"
$PGBIN/psql -ac "create extension phb;"
$PGBIN/psql -ac "\dx+ phb"

echo "--> Now remove tbl1 from the extension"
$PGBIN/psql -ac "alter extension phb drop table phb.tbl1;"

echo "--> The table pushed out of the extension cannot be dropped, neither
the extension"
$PGBIN/psql -ac "drop table phb.tbl1;"
$PGBIN/psql -ac "drop extension phb;"

echo "--> And what if we drop the generated sequence from the extension ?"
$PGBIN/psql -ac "\dx+ phb"
$PGBIN/psql -ac "alter extension phb drop sequence phb.tbl1_col1_seq;"
$PGBIN/psql -ac "\dx+ phb"
$PGBIN/psql -ac "drop extension phb;"
$PGBIN/psql -ac "\d phb.tbl1;"

# cleanup the environment
$PGBIN/psql template1 -ac "drop database phb;"
rm $PGINST/share/postgresql/extension/phb*