[HACKERS] Column/type dependency recording inconsistencies

[Petr Jelinek]

Hi,









postgres=# create extension deptestext ;
CREATE EXTENSION
postgres=# drop extension deptestext;
ERROR:  cannot drop extension deptestext because other objects depend on it

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:


Thoughts?



--
 Petr Jelinek                  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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: DETAIL: table testtable column undroppablecol2 depends on type undroppabletype - 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 Oh and btw looking at the code I think there is same issue with column/collation dependencies.

deptestext--1.0.sql
Description: application/sql

default_version = '1.0'

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