[postgis-tickets] [PostGIS] #3497: Setting search_path on geometry_gist_penalty_2d, geometry_gist_decompress_2d kills update and index creation speed

[PostGIS]

#3497: Setting search_path on geometry_gist_penalty_2d,
geometry_gist_decompress_2d kills update and index creation  speed
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  blocker  |  Milestone:  PostGIS 2.2.2
Component:  postgis  |    Version:  2.2.x
 Keywords:           |
---------------------+---------------------------
 I'm discovering all these annoying things about function search_path.  We
 really need to have some performance seat belts  in our regress so we can
 catch these issues.  I think strk mentioned that concern a while ago.

 Anyrate after my search_path commits for #3490, came across this issue
 which can be replicated as follows:

 -- snippet from generated script causing the issue
 {{{
 DO language plpgsql $$DECLARE param_postgis_schema text;
 BEGIN
 -- check if PostGIS is already installed
 param_postgis_schema = (SELECT n.nspname from pg_extension e join
 pg_namespace n on e.extnamespace = n.oid WHERE extname = 'postgis');

 -- if in middle install, it will be the current_schema or what was there
 already
 param_postgis_schema = COALESCE(param_postgis_schema, current_schema());

 IF param_postgis_schema != current_schema() THEN
         EXECUTE 'set search_path TO ' ||
 quote_ident(param_postgis_schema);
 END IF;

 -- PostGIS set search path of functions

 EXECUTE 'ALTER FUNCTION
 geometry_gist_penalty_2d(internal,internal,internal ) SET search_path=' ||
 quote_ident(param_postgis_schema) || ';';
 EXECUTE 'ALTER FUNCTION  geometry_gist_decompress_2d(internal ) SET
 search_path=' || quote_ident(param_postgis_schema) || ';';

 END;$$;
 }}}

 {{{
 DROP TABLE IF EXISTS test_geom_search_path;
 CREATE TABLE test_geom_search_path(gid serial primary key, geom geometry);

 INSERT INTO test_geom_search_path(geom)
 SELECT ST_Point(i,j)
 FROM generate_series(1,1000) As i, generate_series(20,50) j;

 CREATE INDEX idx_test_geom_search_path_geom_gist ON  test_geom_search_path
 USING gist(geom);  --181 ms w/o search path, 5-8 seconds with search_path
 }}}

 Same thing happens if you do a mindless update on the table even if you
 aren't updating the geometry column.  Evidentally postgres is relying on
 not having function isolation for this one, so I think our best bet is
 just exclude all functions that take internals from search_path magic.

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3497>
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.