BUG #14434: Drop a table with a serial in an extension

The following bug has been logged on the website:

Bug reference: 14434
Logged by: Philippe BEAUDOIN
Email address: phb07(at)apra(dot)asso(dot)fr
PostgreSQL version: 9.6.1
Operating system: Linux Ubuntu
Description:

I reach something strange recently when developing an upgrade script for an
extension.

Let's create a table with a serial column (outside any extension).
Then let's link the table with its sequence to a new extension using ALTER
EXTENSION ADD ... in the extension script.
Even though the documentation is not clear on that point, it is necessary to
explicitly link the sequence to the extension, not to be in trouble with
pg_dump for instance.
So far so good. Everything reacts as if the table had been created inside
the extension.

But an error occurs when I try to drop this table in the next extension
upgrade script:
ERROR: cannot drop table old_tbl1 because other objects depend on it
DETAIL: extension ext1 depends on table old_tbl1
HINT: Use DROP ... CASCADE to drop the dependent objects too.

As a workaround, the sequence can be unlinked from the extension just before
dropping the table.

Here is a test case that shows the issue (just adjust the 3 variables set at
the beginning of the script):
--------------------------------------------------------------------------------

export PGHOST=localhost
export PGPORT=5496
PGEXTENSION="/home/postgres/pg/pg96/share/postgresql/extension"

# Step 1: prepare extension environment
echo "***Prepare extension environment"

cat >/tmp/ext1.control <<EOF0
default_version = '1'
directory = '/tmp'
superuser = true
EOF0
sudo ln -s /tmp/ext1.control $PGEXTENSION/ext1.control

# Step 2: create a table
echo "*** Create 2 initial tables"
psql <<EOF1
select version();
drop table if exists old_tbl1;
create table old_tbl1 (col1 serial);
drop table if exists old_tbl2;
create table old_tbl2 (col2 serial);
EOF1

# Step 3: create the extension from unpackaged
echo "*** Create the extension"
cat >/tmp/ext1--unpackaged--1.sql <<EOF2
-- link both old tables but only first sequence to the extension
alter extension ext1 add table old_tbl1;
alter extension ext1 add sequence old_tbl1_col1_seq;
alter extension ext1 add table old_tbl2;
-- create another regular table with a serial column
drop table if exists new_tbl3;
create table new_tbl3 (col3 serial);
EOF2

psql <<EOF3
create extension ext1 from unpackaged;
\dx ext1
\echo list dependancies with the old_tbl1 table and its sequence
select
pg_describe_object(classid,objid,objsubid),pg_describe_object(refclassid,refobjid,refobjsubid),*
from pg_depend
where deptype <> 'n' and
(pg_describe_object(classid,objid,objsubid) = 'table old_tbl1'
or pg_describe_object(classid,objid,objsubid) = 'sequence
old_tbl1_col1_seq')
order by 1,2;

\echo list dependancies with the old_tbl2 table and its sequence
select
pg_describe_object(classid,objid,objsubid),pg_describe_object(refclassid,refobjid,refobjsubid),*
from pg_depend
where deptype <> 'n' and
(pg_describe_object(classid,objid,objsubid) = 'table old_tbl2'
or pg_describe_object(classid,objid,objsubid) = 'sequence
old_tbl2_col2_seq')
order by 1,2;

\echo list dependancies with the new_tbl3 table and its sequence
select
pg_describe_object(classid,objid,objsubid),pg_describe_object(refclassid,refobjid,refobjsubid),*
from pg_depend
where deptype <> 'n' and
(pg_describe_object(classid,objid,objsubid) = 'table new_tbl3'
or pg_describe_object(classid,objid,objsubid) = 'sequence
new_tbl3_col3_seq')
order by 1,2;
\echo This reflects correctly the expected dependancies (at least to me)
EOF3

echo "*** pg_dump effectively doesn't consider the 2nd sequence as member of
the extension"
pg_dump |grep -P 'SEQUENCE .*tbl\d_col\d_seq'

# Step 4: update the extension
echo "*** Try to update the extension"
cat >/tmp/ext1--1--2.sql <<EOF4
drop table old_tbl2;
-- alter extension ext1 drop sequence old_tbl1_col1_seq;
drop table old_tbl1;
EOF4

psql <<EOF5
alter extension ext1 update to '2';
\dx ext1
EOF5

echo "*** End of test"
psql -c "drop extension ext1;"
sudo rm $PGEXTENSION/ext1.control
rm /tmp/ext1*

----------------------------------------------------------------------------------
And at execution time:

***Prepare extension environment
*** Create 2 initial tables
version

-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

NOTICE: table "old_tbl1" does not exist, skipping
DROP TABLE
CREATE TABLE
NOTICE: table "old_tbl2" does not exist, skipping
DROP TABLE
CREATE TABLE
*** Create the extension
CREATE EXTENSION
List of installed extensions
Name | Version | Schema | Description
------+---------+--------+-------------
ext1 | 1 | public |
(1 row)

list dependancies with the old_tbl1 table and its sequence
pg_describe_object | pg_describe_object | classid | objid
| objsubid | refclassid | refobjid | refobjsubid | deptype
----------------------------+----------------------------+---------+--------+----------+------------+----------+-------------+---------
sequence old_tbl1_col1_seq | extension ext1 | 1259 | 161565
| 0 | 3079 | 161577 | 0 | e
sequence old_tbl1_col1_seq | table old_tbl1 column col1 | 1259 | 161565
| 0 | 1259 | 161567 | 1 | a
table old_tbl1 | extension ext1 | 1259 | 161567
| 0 | 3079 | 161577 | 0 | e
(3 rows)

list dependancies with the old_tbl2 table and its sequence
pg_describe_object | pg_describe_object | classid | objid
| objsubid | refclassid | refobjid | refobjsubid | deptype
----------------------------+----------------------------+---------+--------+----------+------------+----------+-------------+---------
sequence old_tbl2_col2_seq | table old_tbl2 column col2 | 1259 | 161571
| 0 | 1259 | 161573 | 1 | a
table old_tbl2 | extension ext1 | 1259 | 161573
| 0 | 3079 | 161577 | 0 | e
(2 rows)

list dependancies with the new_tbl3 table and its sequence
pg_describe_object | pg_describe_object | classid | objid
| objsubid | refclassid | refobjid | refobjsubid | deptype
----------------------------+----------------------------+---------+--------+----------+------------+----------+-------------+---------
sequence new_tbl3_col3_seq | extension ext1 | 1259 | 161578
| 0 | 3079 | 161577 | 0 | e
sequence new_tbl3_col3_seq | table new_tbl3 column col3 | 1259 | 161578
| 0 | 1259 | 161580 | 1 | a
table new_tbl3 | extension ext1 | 1259 | 161580
| 0 | 3079 | 161577 | 0 | e
(3 rows)

This reflects correctly the expected dependancies (at least to me)
*** pg_dump effectively doesn't consider the 2nd sequence as member of the
extension
CREATE SEQUENCE old_tbl2_col2_seq
*** Try to update the extension
ERROR: cannot drop table old_tbl1 because other objects depend on it
DETAIL: extension ext1 depends on table old_tbl1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
List of installed extensions
Name | Version | Schema | Description
------+---------+--------+-------------
ext1 | 1 | public |
(1 row)

*** End of test
DROP EXTENSION