postgis should only be allowed to be installed only once per database

[ardell]

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@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