Skip site navigation (1) Skip section navigation (2)

BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves danglingrelations

From: jeff(at)pgexperts(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves danglingrelations
Date: 2012-06-23 00:36:36
Message-ID: E1SiEL6-0002u2-Ov@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6704
Logged by:          Jeff Frost
Email address:      jeff(at)pgexperts(dot)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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-06-23 02:37:10
Subject: Re: BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Previous:From: Kevin GrittnerDate: 2012-06-22 15:12:52
Subject: Re: BUG #6702: SELECT Query on INDEX

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group