[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