Opened 4 years ago

Closed 4 years ago

#3497 closed defect (fixed)

Setting search_path on geometry_gist_penalty_2d, geometry_gist_decompress_2d kills update and index creation speed

Reported by: robe Owned by: robe
Priority: blocker Milestone: PostGIS 2.2.2
Component: postgis Version: 2.2.x
Keywords: Cc:

Description

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.

Change History (4)

comment:1 Changed 4 years ago by robe

Owner: changed from pramsey to robe

comment:2 Changed 4 years ago by robe

(In [14768]) avoid adding search_path to functions that work on internals references #3497

comment:3 Changed 4 years ago by robe

(In [14769]) avoid adding search_path to functions that work on internals (fix typo in last commit) references #3497

comment:4 Changed 4 years ago by robe

Resolution: fixed
Status: newclosed

(In [14770]) avoid adding search_path to functions that take internals for input closes #3497 for 2.2 reference #3490 cleanup avoid drop* functsion as well.

Note: See TracTickets for help on using tickets.