Hi,
While working on an extension upgrade script I got hit by what I believe
is a bug in the way we record dependencies between columns and types.
CREATE TABLE records dependency between relation and type, not between
column and type, but ALTER TABLE ADD COLUMN and ALTER TABLE ALTER COLUMN
TYPE record dependencies between relation column and type and not
between relation and type
Now this might seem harmless (and apparently is most of the time), but
there is one case where this breaks things - extensions.
If one creates type in an extension and then uses that type for some
column in CREATE TABLE statement, everything is fine. But if one then
uses that type in either ALTER TABLE ADD COLUMN or ALTER TABLE ALTER
COLUMN TYPE then the extension becomes undroppable without CASCADE which
is clearly wrong.
I attached sample extension that demonstrates this problem. Output of my
psql console when creating/dropping this extension:
postgres=# create extension deptestext ;
CREATE EXTENSION
postgres=# drop extension deptestext;
ERROR: cannot drop extension deptestext because other objects depend on it
DETAIL: table testtable column undroppablecol2 depends on type
undroppabletype
table testtable column undroppablecol1 depends on type undroppabletype
HINT: Use DROP ... CASCADE to drop the dependent objects too.
I see two possible fixes for this:
- either record relation/type dependency for ALTER TABLE ADD COLUMN
and ALTER TABLE ALTER COLUMN TYPE statements instead of column/type one
- or record dependency between the column and extension for ALTER
TABLE ADD COLUMN and ALTER TABLE ALTER COLUMN TYPE statements
Thoughts?
Oh and btw looking at the code I think there is same issue with
column/collation dependencies.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
