[BUGS] objects tied to missing extension

[Phil Sorber]

I stumbled upon this situation when playing with extension upgrades.
The problem I was having was that auto-generated datatypes were also
being added to the extension and it wasn't obvious this was happening.
I know this has been changed in 9.1 stable and in master.

What happened was that I was able to delete the extension in the
upgrade script, either by dropping a table with cascade or by outright
dropping the extension. It's debatable whether or not that should be
allowed, but what happens afterward is what I am more concerned about.
In the same upgrade script, after dropping the extension, I created
another table and now it is tied to an extension that no longer
exists. The output of my psql session is below and the extension files
are attached.

postgres=# select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.6.0 20110603 (Red Hat 4.6.0-10), 64-bit
(1 row)

postgres=# create database bug_example;
CREATE DATABASE
postgres=# \c bug_example
You are now connected to database "bug_example" as user "postgres".
bug_example=# create extension bug_example with version '1.0';
CREATE EXTENSION
bug_example=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | table_a        | table    | postgres
 public | table_a_id_seq | sequence | postgres
(2 rows)

bug_example=# alter extension bug_example update to '2.0';
ALTER EXTENSION
bug_example=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | table_b        | table    | postgres
 public | table_b_id_seq | sequence | postgres
(2 rows)

bug_example=# drop extension bug_example;
ERROR:  extension "bug_example" does not exist
bug_example=# drop table table_b;
ERROR:  cache lookup failed for extension 17439
bug_example=# drop table table_b cascade;
ERROR:  cache lookup failed for extension 17439

bug_example.control
Description: Binary data

create table table_a ( id serial, primary key (id) );

create table table_b ( id serial, primary key (id) );

alter extension bug_example drop table table_a;
alter extension bug_example drop sequence table_a_id_seq;

drop table table_a cascade;

--drop extension bug_example;

create table table_b ( id serial, primary key (id) );

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs