Обсуждение: 9.1rc1 bug: extension types not dropped with DROP SCHEMA CASCADE

Hi!

Creating an extension in a schema and then dropping that schema
apparently doesn't clean up the extension's types:
db=# CREATE SCHEMA foo;
db=# CREATE EXTENSION cube WITH SCHEMA foo;
db=# DROP SCHEMA foo CASCADE;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to operator foo.<>(foo.cube,foo.cube)
drop cascades to operator foo.>(foo.cube,foo.cube)
drop cascades to operator foo.<=(foo.cube,foo.cube)
drop cascades to operator foo.>=(foo.cube,foo.cube)
drop cascades to operator foo.<@(foo.cube,foo.cube)
drop cascades to operator foo.~(foo.cube,foo.cube)

It leaves around pg_type entry with typnamespace that doesn't exist in
pg_namespace:

db=# select * from pg_type left join pg_namespace ns on
(typnamespace=ns.oid) where ns.oid is null;
* Record 1
typname        _cube
typnamespace   17074
typowner       10
typlen         -1
...

Also breaks pg_dump: "pg_dump: schema with OID 17074 does not exist"

Regards,
Marti

On 10 September 2011 12:46, Marti Raudsepp <marti@juffo.org> wrote:
> Hi!
>
> Creating an extension in a schema and then dropping that schema
> apparently doesn't clean up the extension's types:
> db=# CREATE SCHEMA foo;
> db=# CREATE EXTENSION cube WITH SCHEMA foo;
> db=# DROP SCHEMA foo CASCADE;
> NOTICE:  drop cascades to 6 other objects
> DETAIL:  drop cascades to operator foo.<>(foo.cube,foo.cube)
> drop cascades to operator foo.>(foo.cube,foo.cube)
> drop cascades to operator foo.<=(foo.cube,foo.cube)
> drop cascades to operator foo.>=(foo.cube,foo.cube)
> drop cascades to operator foo.<@(foo.cube,foo.cube)
> drop cascades to operator foo.~(foo.cube,foo.cube)
>
> It leaves around pg_type entry with typnamespace that doesn't exist in
> pg_namespace:
>
> db=# select * from pg_type left join pg_namespace ns on
> (typnamespace=ns.oid) where ns.oid is null;
> * Record 1
> typname        _cube
> typnamespace   17074
> typowner       10
> typlen         -1
> ...
>
> Also breaks pg_dump: "pg_dump: schema with OID 17074 does not exist"

I can confirm the problem in 9.1rc1, but it's fine when testing it on
9.1.0 which is out on Monday, and Git master.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Marti Raudsepp <marti@juffo.org> writes:
> Creating an extension in a schema and then dropping that schema
> apparently doesn't clean up the extension's types:

I believe this bug has already been fixed, but the fix is not in rc1.
 http://archives.postgresql.org/pgsql-bugs/2011-08/msg00120.php
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=660a081c3f6892dae353136fa0883cd3c69813d

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

On Sat, Sep 10, 2011 at 15:44, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> I believe this bug has already been fixed, but the fix is not in rc1.

Thanks Thom and Dimitri, I'll make sure to test my bug reports with
git master in the future. :)

Regards,
Marti