id,summary,reporter,owner,description,type,status,priority,milestone,component,version,resolution,keywords,cc 2580,postgis should only be allowed to be installed only once per database,ardell,strk,"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@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",defect,closed,medium,PostGIS 2.1.2,build,2.1.x,fixed,,