>> On Fri, Mar 4, 2016 at 9:29 PM, Regina Obe <l...@pcorp.us>> wrote: >> I think the answer to this question is NO, but thought I'd ask. >> >> A lot of folks in PostGIS land are suffering from restore issues, >> materialized view issues etc. because we have functions such as >> >> ST_Intersects >> >> Which does _ST_Intersects AND && >> >> Since _ST_Intersects is not schema qualified, during database restore >> (which sets the schema to the table or view schema), materialized >> views that depend on this do not come back.
> Could you provide a self-contained, reproducible test case that illustrates > this problem? Ideally, one that doesn't involve installing PostGIS? > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Here is a script just involving the built in geometric types that has the same issue: -- script starts here -- CREATE schema funcs; set search_path=public,funcs; CREATE OR REPLACE FUNCTION funcs._helper(box, box) RETURNS float8 AS $$ SELECT box_distance($1,$2); $$ language 'sql' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION funcs.no_inline(box,box) RETURNS boolean AS $$ SELECT $1 && $2 AND _helper($1,$2) = 0; $$ language 'sql' IMMUTABLE; --doing this kills inlining ALTER FUNCTION funcs.no_inline(box, box) SET search_path=funcs; --this one doesn't have search_path set so inlining works CREATE OR REPLACE FUNCTION funcs.inline(box,box) RETURNS boolean AS $$ SELECT $1 && $2 AND _helper($1,$2) = 0; $$ language 'sql' IMMUTABLE; CREATE TABLE bag_boxes(id serial primary key, geom box); CREATE INDEX idx_bag_boxes_geom ON bag_boxes USING gist(geom); INSERT INTO bag_boxes(geom) SELECT ('((' || i::text || ',' || j::text || '), (' || k::text || ', ' || l::text || '))')::box FROM generate_series(1,10) i , generate_series(11,20) j, generate_series(5,10) k, generate_series(10, 15) l ; SELECT b1.id, b2.id As id2 FROM bag_boxes AS b1 INNER JOIN bag_boxes As b2 ON no_inline(b1.geom, b2.geom); -- plan looks like this -- PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 32-bit /** Nested Loop (cost=0.00..3402141.00 rows=4320000 width=8) Join Filter: no_inline(b1.geom, b2.geom) -> Seq Scan on bag_boxes b1 (cost=0.00..66.00 rows=3600 width=36) -> Materialize (cost=0.00..84.00 rows=3600 width=36) -> Seq Scan on bag_boxes b2 (cost=0.00..66.00 rows=3600 width=36) **/ SELECT b1.id, b2.id As id2 FROM bag_boxes AS b1 INNER JOIN bag_boxes As b2 ON inline(b1.geom, b2.geom); -- plan looks like this PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 32-bit /** Nested Loop (cost=0.15..2359.00 rows=324 width=8) -> Seq Scan on bag_boxes b1 (cost=0.00..66.00 rows=3600 width=36) -> Index Scan using idx_bag_boxes_geom on bag_boxes b2 (cost=0.15..0.63 rows=1 width=36) Index Cond: (b1.geom && geom) Filter: (box_distance(b1.geom, geom) = '0'::double precision) **/ -- end script -- Thanks, Regina -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers