The following bug has been logged on the website: Bug reference: 6704 Logged by: Jeff Frost Email address: j...@pgexperts.com PostgreSQL version: 9.1.4 Operating system: Windows and Linux Description:
DROP and CREATE extension appear to work fine, but if you ALTER EXTENSION postgis SET SCHEMA foo, it leaves a few relations behind. Then if you drop that schema, you can't pg_dump the DB anymore. See reproducible test case below. Note a the bottom that even though the ALTER left items in the original schema, I'm able to drop that schema without CASCADE and also if I then DROP EXTENSION, it happily gets rid of those. Test case: pgx-test:~ $ createdb ext_test pgx-test:~ $ psql ext_test psql (9.1.4) Type "help" for help. ext_test=# create schema test; CREATE SCHEMA Time: 27.736 ms ext_test=# create EXTENSION postgis with schema test; CREATE EXTENSION Time: 764.102 ms ext_test=# alter EXTENSION postgis set schema public; ALTER EXTENSION Time: 221.224 ms ext_test=# select oid, nspname from pg_namespace ; oid | nspname ---------+-------------------- 99 | pg_toast 11124 | pg_temp_1 11125 | pg_toast_temp_1 11 | pg_catalog 2200 | public 12257 | information_schema 6981446 | test (7 rows) Time: 0.256 ms ext_test=# select oid, relname, relnamespace from pg_class where relnamespace = 6981446; oid | relname | relnamespace ---------+----------------------+-------------- 6981694 | spatial_ref_sys_pkey | 6981446 (1 row) Time: 36.072 ms ext_test=# select oid, proname, pronamespace from pg_proc where pronamespace = 6981446; oid | proname | pronamespace -----+---------+-------------- (0 rows) Time: 7.797 ms ext_test=# select oid, typname, typnamespace from pg_type where typnamespace = 6981446; oid | typname | typnamespace ---------+--------------------+-------------- 6981689 | spatial_ref_sys | 6981446 6981688 | _spatial_ref_sys | 6981446 6981995 | geography_columns | 6981446 6981994 | _geography_columns | 6981446 6982099 | geometry_columns | 6981446 6982098 | _geometry_columns | 6981446 6982541 | raster_columns | 6981446 6982540 | _raster_columns | 6981446 6982550 | raster_overviews | 6981446 6982549 | _raster_overviews | 6981446 (10 rows) Time: 7.844 ms ext_test=# select oid, conname, connamespace from pg_constraint where connamespace = 6981446; oid | conname | connamespace ---------+----------------------------+-------------- 6981690 | spatial_ref_sys_srid_check | 6981446 6981695 | spatial_ref_sys_pkey | 6981446 (2 rows) Time: 0.201 ms ext_test=# DROP EXTENSION postgis ; DROP EXTENSION Time: 214.645 ms ext_test=# select oid, relname, relnamespace from pg_class where relnamespace = 6981446; oid | relname | relnamespace -----+---------+-------------- (0 rows) Time: 49.484 ms ext_test=# select oid, proname, pronamespace from pg_proc where pronamespace = 6981446; oid | proname | pronamespace -----+---------+-------------- (0 rows) Time: 7.698 ms ext_test=# select oid, typname, typnamespace from pg_type where typnamespace = 6981446; oid | typname | typnamespace -----+---------+-------------- (0 rows) Time: 7.864 ms ext_test=# select oid, conname, connamespace from pg_constraint where connamespace = 6981446; oid | conname | connamespace -----+---------+-------------- (0 rows) Time: 0.144 ms ext_test=# ext_test=# \q -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs