Re: BUG #6172: DROP EXTENSION error without CASCADE

"Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com> writes:
> On pure-installed RC1 database, you can CREATE EXTENSION, but cannot DROP
> it.
>
> CREATE EXTENSION cube;
> DROP EXTENSION cube;
>
> ERROR: cannot drop extension cube because other objects depend on it

I confirm I have the same bug in current HEAD.

Reading the code, my gut feeling is that the bug sits in
findDependentObjects(), in this part of it:

/*
* Okay, recurse to the other object instead of proceeding. We
* treat this exactly as if the original reference had linked
* to that object instead of this one; hence, pass through the
* same flags and stack.
*/

The extension cube depends on some operator that depend on some function
implementing them, and as the initial dependency delete call is not
explicitly mentioning them, then it behaves as if CASCADE was needed.

Also, \dx+ cube will not show all the operators and functions. It skips
those that we see in the CASCADE error message listing. Here's the SQL
query that will list the object with a direct extension dependency
towards the extension, here of OID 16385.

dim=# SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS "Object Description"
dim-# FROM pg_catalog.pg_depend
dim-# WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '16385' AND deptype = 'e'
dim-# ORDER BY 1;

Of course we didn't have that problem when we added extensions in (that
I remember of), so I'm now going to try and find when that did change…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support