[postgis-tickets] [PostGIS] #2580: postgis should only be allowed to be installed only once per database

PostGIS trac at osgeo.org
Wed Dec 18 07:24:08 PST 2013


#2580: postgis should only be allowed to be installed only once per database
---------------------+------------------------------------------------------
 Reporter:  ardell   |       Owner:  strk         
     Type:  defect   |      Status:  new          
 Priority:  medium   |   Milestone:  PostGIS 2.1.2
Component:  postgis  |     Version:  2.1.x        
 Keywords:           |  
---------------------+------------------------------------------------------
Description changed by robe:

Old description:

> It is currently possible to install PostGIS twice if installed in
> separate schemas.  This is more of an issue if people use the old way
> (install in public schema for example )  and then try to install with
> CREATE EXTENSION in a non-public schema.  never an issue with a pure
> CREATE EXTENSION since CREATE EXTENSION postgis can only be run once per
> database regardless of schema it is installed in.
>
> See IRC transcript below...
>
> ardell
> i'm trying to use st_simplify, but I think something is wrong with the
> geometry type I'm passing in (postgis.geometry vs geometry)...
> select st_simplify(polygon, 1.0) from areas where title='90210';
> ERROR:  function st_simplify(postgis.geometry, numeric) does not exist
> LINE 1: select st_simplify(polygon, 1.0) from areas where title='902...
>                ^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
> any ideas?
>
> strk
> select postgis_full_version();
>
> ardell
> select postgis_full_version();
> -[ RECORD 1
> ]--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> postgis_full_version | POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2
> r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released
> 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER
>
> strk
> select proname, proargnames from pg_proc where proname = 'st_simplify';
> select proname, proargnames, proargtypes from pg_proc where proname =
> 'st_simplify';
>
> ardell
> select proname, proargnames, proargtypes from pg_proc where proname =
> 'st_simplify';
>    proname   |  proargnames   | proargtypes
> -------------+----------------+-------------
>  st_simplify |                | 223484 701
>  st_simplify |                | 258173 701
>  st_simplify | {tg,tolerance} | 259492 701
> tg = topogeometry?
>
> strk
> select oid, typname from pg_type where oid in (701,223484,258173,259492);
> you should have only 2 in a new install
>
> ardell
> select oid, typname from pg_type where oid in (701,223484,258173,259492);
>   oid   |   typname
> --------+--------------
>     701 | float8
>  223484 | geometry
>  258173 | geometry
>  259492 | topogeometry
>
> strk
> one of the two you have with no proargnames should go away
> select n.nspname, t.typname from pg_type t, pg_namespace n where n.oid =
> t.typnamespace and t.typname = 'geometry';
>
> ardell
> select n.nspname, t.typname from pg_type t, pg_namespace n where n.oid =
> t.typnamespace and t.typname = 'geometry';
>  nspname | typname
> ---------+----------
>  public  | geometry
>  postgis | geometry
>
> strk
> mess
> how was that built ?
> how could we help avoiding that ?
> and (what matters to you most): how can you clean that up ?
> did you install from extension ?
>
> ardell
> homebrew… although I later followed the instructions here
> (http://postgis.net/docs/postgis_installation.html) to CREATE EXTENSION…
> which may have already been done for me
>
> sigq
> Title: Chapter?2.?PostGIS Installation (at postgis.net)
>
> strk
> extension is harmful
>
> ardell
> ohhh
>
> strk
> personal opinion, don't take for truth
>
> ardell
> what would you recommend instead? the old-fashioned instructions?
>
> strk
> test: create db, install old way, create extension
>
> ardell
> createlang plpgsql yourdatabase...
>
> strk
> yeah
> but it's too late !
> do you have data in that db ?
>
> ardell
> it's a dev box i can trash the install
>
> strk
> can you run the test above ?
> 1) install old way
> 2) create extension postgis
> does that re-create the mess ?
>
> ardell
> sure i can try that, will take some time
>
> strk
> I just did
>
> robe2
> if you always install with extensions, you can't install postgis twice
> (even if in different schemas)
>
> strk
> no, it doesn't work
> ttt=# create extension postgis;
> ERROR:  type "spheroid" already exists
> so that's not how the mess was created
>
> robe2
> with old way you could conceivably install in many schemas
>
> strk
> I'll try the other way around then
> nope, can't mess up with that either
>
> robe2
> strk: you could recreate the mess by install old way in public
>
> strk
> [strk at cdb:/usr/src/postgis/postgis(svn-trunk)] psql ttt -f
> postgis/postgis.sql --set ON_ERROR_STOP=1
> ...
> psql:postgis/postgis.sql:80: ERROR:  type "spheroid" already exists
>
> robe2
> create extensions postgis schema postgis
>
> strk
> ttt=# create extension postgis schema postgis;
> ERROR:  schema "postgis" does not exist
> ttt=# create extension postgis schema postgis;
> CREATE EXTENSION
>
> robe2
> strk you have to create the schema first
>
> strk
> bingo!
> alright, so robe2, how can we help avoiding this ?
> should postgis_full_version() check that ?
>
> robe2
> I've noticed a lot of tools - e.g. django and ruby seem to standardize on
> installing postgis in schema postgis
>
> strk
> check if more than a single "postgis_full_version" function exists
> and warn about that, reporting schema names
>
> robe2
> strk: get rid of the old way
>
> strk
> nah
> no way
>
> robe2
> strk: for example if people can only install using extensions then they
> can't do: CREATE EXTENSION postgis; CREATE EXTENSION postgis schema
> postgis;
>
> strk
> it's the only one I trust, the only one that lets you install in a
> foreign system, where you don't control the install paths
>
> robe2
> because the extension is already registered first time
>
> strk
> if people can only install using extensions PostGIS would become unusable
> for somebody
> and untestable pre-install
>
> robe2
> strk: agreed but I think for new people its safer to push them to
> extensions
>
> nhv
> can't you check to see if postgis is installed already and refuse to
> install it twice
> albertid left the room (quit: Remote host closed the connection).
>
> strk
> nhv: right
> likes nhv way of thinking
>
> robe2
> for experienced people like you you have more advanced needs, and well
> you are smart enough not to screw yourself we hope
>
> strk
> so we need
> 1) a ticket
> 2) an enhanced postgis_full_version() to detect duplicated installs
> 3) protection against duplicated install

New description:

 It is currently possible to install PostGIS twice if installed in separate
 schemas.  This is more of an issue if people use the old way (install in
 public schema for example )  and then try to install with CREATE EXTENSION
 in a non-public schema.  never an issue with a pure CREATE EXTENSION since
 CREATE EXTENSION postgis can only be run once per database regardless of
 schema it is installed in.

 Proposed solution to satisfy all camps, is that on full installation of
 postgis, postgis_full_version should be checked if it exists in any schema
 and then postgis install should fail if it finds it.

 See IRC transcript below...

 ardell
 i'm trying to use st_simplify, but I think something is wrong with the
 geometry type I'm passing in (postgis.geometry vs geometry)...
 select st_simplify(polygon, 1.0) from areas where title='90210';
 ERROR:  function st_simplify(postgis.geometry, numeric) does not exist
 LINE 1: select st_simplify(polygon, 1.0) from areas where title='902...
                ^
 HINT:  No function matches the given name and argument types. You might
 need to add explicit type casts.
 any ideas?

 strk
 select postgis_full_version();

 ardell
 select postgis_full_version();
 -[ RECORD 1
 ]--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 postgis_full_version | POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2
 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released
 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER

 strk
 select proname, proargnames from pg_proc where proname = 'st_simplify';
 select proname, proargnames, proargtypes from pg_proc where proname =
 'st_simplify';

 ardell
 select proname, proargnames, proargtypes from pg_proc where proname =
 'st_simplify';
    proname   |  proargnames   | proargtypes
 -------------+----------------+-------------
  st_simplify |                | 223484 701
  st_simplify |                | 258173 701
  st_simplify | {tg,tolerance} | 259492 701
 tg = topogeometry?

 strk
 select oid, typname from pg_type where oid in (701,223484,258173,259492);
 you should have only 2 in a new install

 ardell
 select oid, typname from pg_type where oid in (701,223484,258173,259492);
   oid   |   typname
 --------+--------------
     701 | float8
  223484 | geometry
  258173 | geometry
  259492 | topogeometry

 strk
 one of the two you have with no proargnames should go away
 select n.nspname, t.typname from pg_type t, pg_namespace n where n.oid =
 t.typnamespace and t.typname = 'geometry';

 ardell
 select n.nspname, t.typname from pg_type t, pg_namespace n where n.oid =
 t.typnamespace and t.typname = 'geometry';
  nspname | typname
 ---------+----------
  public  | geometry
  postgis | geometry

 strk
 mess
 how was that built ?
 how could we help avoiding that ?
 and (what matters to you most): how can you clean that up ?
 did you install from extension ?

 ardell
 homebrew… although I later followed the instructions here
 (http://postgis.net/docs/postgis_installation.html) to CREATE EXTENSION…
 which may have already been done for me

 sigq
 Title: Chapter?2.?PostGIS Installation (at postgis.net)

 strk
 extension is harmful

 ardell
 ohhh

 strk
 personal opinion, don't take for truth

 ardell
 what would you recommend instead? the old-fashioned instructions?

 strk
 test: create db, install old way, create extension

 ardell
 createlang plpgsql yourdatabase...

 strk
 yeah
 but it's too late !
 do you have data in that db ?

 ardell
 it's a dev box i can trash the install

 strk
 can you run the test above ?
 1) install old way
 2) create extension postgis
 does that re-create the mess ?

 ardell
 sure i can try that, will take some time

 strk
 I just did

 robe2
 if you always install with extensions, you can't install postgis twice
 (even if in different schemas)

 strk
 no, it doesn't work
 ttt=# create extension postgis;
 ERROR:  type "spheroid" already exists
 so that's not how the mess was created

 robe2
 with old way you could conceivably install in many schemas

 strk
 I'll try the other way around then
 nope, can't mess up with that either

 robe2
 strk: you could recreate the mess by install old way in public

 strk
 [strk at cdb:/usr/src/postgis/postgis(svn-trunk)] psql ttt -f
 postgis/postgis.sql --set ON_ERROR_STOP=1
 ...
 psql:postgis/postgis.sql:80: ERROR:  type "spheroid" already exists

 robe2
 create extensions postgis schema postgis

 strk
 ttt=# create extension postgis schema postgis;
 ERROR:  schema "postgis" does not exist
 ttt=# create extension postgis schema postgis;
 CREATE EXTENSION

 robe2
 strk you have to create the schema first

 strk
 bingo!
 alright, so robe2, how can we help avoiding this ?
 should postgis_full_version() check that ?

 robe2
 I've noticed a lot of tools - e.g. django and ruby seem to standardize on
 installing postgis in schema postgis

 strk
 check if more than a single "postgis_full_version" function exists
 and warn about that, reporting schema names

 robe2
 strk: get rid of the old way

 strk
 nah
 no way

 robe2
 strk: for example if people can only install using extensions then they
 can't do: CREATE EXTENSION postgis; CREATE EXTENSION postgis schema
 postgis;

 strk
 it's the only one I trust, the only one that lets you install in a foreign
 system, where you don't control the install paths

 robe2
 because the extension is already registered first time

 strk
 if people can only install using extensions PostGIS would become unusable
 for somebody
 and untestable pre-install

 robe2
 strk: agreed but I think for new people its safer to push them to
 extensions

 nhv
 can't you check to see if postgis is installed already and refuse to
 install it twice
 albertid left the room (quit: Remote host closed the connection).

 strk
 nhv: right
 likes nhv way of thinking

 robe2
 for experienced people like you you have more advanced needs, and well you
 are smart enough not to screw yourself we hope

 strk
 so we need
 1) a ticket
 2) an enhanced postgis_full_version() to detect duplicated installs
 3) protection against duplicated install

--

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2580#comment:4>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.


More information about the postgis-tickets mailing list