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(dot)emaj(at)free(dot)fr
PostgreSQL version: 9.1.4
Operating system: Linux
Description:

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.

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=/usr/local/pg913
export PGBIN=$PGINST/bin
export PGEXT=$PGINST/share/postgresql/extension

export PGHOST=localhost
export PGPORT=5913
export PGUSER=postgres
export PGDATABASE=phb

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
default_version = '1.0.0'
schema = 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
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*