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
