[postgis-tickets] [PostGIS] #3496: Make postgis non-relocateable and schema qualify at least core functions

[PostGIS]

#3496: Make postgis non-relocateable and schema qualify at least core functions
------------------------------------+---------------------------
  Reporter:  robe                   |      Owner:  robe
      Type:  defect                 |     Status:  new
  Priority:  high                   |  Milestone:  PostGIS 2.3.0
 Component:  build/upgrade/install  |    Version:  trunk
Resolution:                         |   Keywords:
------------------------------------+---------------------------
Description changed by robe:

Old description:

> I misunderstood the docs a bit.  I blame pgAdmin for my stupidity because
> it always greyed out the schema feature in extension gui if an extension
> is marked non-relocatable.
>
> I did some experiments, and it does seem that we can schema qualify our
> functions, without forcing users to install postgis in postgis schema.
> Though I still think that is where we want to move for the sake of other
> extensions that may rely on postgis.
>

> Sooo if we have our control file look like this:
>

> {{{
> # postgis extension
> comment = 'PostGIS geometry, geography, and raster spatial types and
> functions'
> default_version = '2.3.0dev'
> module_pathname = '$libdir/postgis-2.3'
> relocatable = false
> }}}
>
> Then we can use the variable @extschema@ in lieu of the actual schema
> name.  This will still allow users to do:
>

> {{{
> CREATE EXTENSION postgis SCHEMA whereever_I_damn_want_you_to_be;
> }}}
>

> And if I then have ST_Intersects rewritten as:
>

> {{{
> CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry)
>         RETURNS boolean
>         AS 'SELECT $1 && $2 AND @extschema at ._ST_Intersects($1,$2)'
>         LANGUAGE 'sql' IMMUTABLE;
> }}}
>

> Once installed, in the desired user location, it will then become:
>

> {{{
> CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry)
>         RETURNS boolean
>         AS 'SELECT $1 && $2 AND
> whereever_I_damn_want_you_to_be._ST_Intersects($1,$2)'
>         LANGUAGE 'sql' IMMUTABLE;
> }}}
>
> upon installation.  The downside being, the user can't then do:
>

> {{{
> ALTER EXTENSION postgis SET schema public;
> }}}
>
> They'll get a
>

> {{{
> ERROR:  extension "postgis" does not support SET SCHEMA
> }}}
>

> Now how to make this work nicely with non-extension piece.
>
> I suppose when we are creating the scripts for regular old postgis
> install.

New description:

 I misunderstood the docs a bit.  I blame pgAdmin for my stupidity because
 it always greyed out the schema feature in extension gui if an extension
 is marked non-relocatable.

 I did some experiments, and it does seem that we can schema qualify our
 functions, without forcing users to install postgis in postgis schema.
 Though I still think that is where we want to move for the sake of other
 extensions that may rely on postgis.


 Sooo if we have our control file look like this:


 {{{
 # postgis extension
 comment = 'PostGIS geometry, geography, and raster spatial types and
 functions'
 default_version = '2.3.0dev'
 module_pathname = '$libdir/postgis-2.3'
 relocatable = false
 }}}

 Then we can use the variable @extschema@ in lieu of the actual schema
 name.  This will still allow users to do:


 {{{
 CREATE EXTENSION postgis SCHEMA whereever_I_damn_want_you_to_be;
 }}}


 And if I then have ST_Intersects rewritten as:


 {{{
 CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry)
         RETURNS boolean
         AS 'SELECT $1 && $2 AND @extschema at ._ST_Intersects($1,$2)'
         LANGUAGE 'sql' IMMUTABLE;
 }}}


 Once installed, in the desired user location, it will then become:


 {{{
 CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry)
         RETURNS boolean
         AS 'SELECT $1 && $2 AND
 whereever_I_damn_want_you_to_be._ST_Intersects($1,$2)'
         LANGUAGE 'sql' IMMUTABLE;
 }}}

 upon installation.  The downside being, the user can't then do:


 {{{
 ALTER EXTENSION postgis SET schema public;
 }}}

 They'll get a


 {{{
 ERROR:  extension "postgis" does not support SET SCHEMA
 }}}


 Now how to make this work nicely with non-extension piece.

 I have some thoughts.

 1) Have all our schema qualified calls have @extschema@ to follow
 extension convention.

 2) Have a build-time extension schema value that defaults to nothing

 3) Instead of having the perl build script run once and get used for both
 extension and non-extension, it would be changed to run twice.

 4) If no extension schema is provided (and it's a regular non-extension
 build run), the perl scripts will just wipe out  @extschema at .  If there is
 a build time specified it will replace @extschema@ with the build time
 version.

 5) the the control files, if there is a build-time schema specified (it
 will put this in the generated control file) -- this is more for future.

--

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3496#comment:3>
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.