Is there a way around function search_path killing SQL function inlining?

From: "Regina Obe" <lr(at)pcorp(dot)us>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Is there a way around function search_path killing SQL function inlining?
Date: 2016-03-05 02:29:28
Message-ID: 000001d17686$d7fdba40$87f92ec0$@pcorp.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.
It's also a serious issue with raster, though that one can be fixed by
setting search_path since the issue there doesn't use SQL inlining.

So I had this bright idea of setting the search_path of the functions to
where PostGIS is installed.

https://trac.osgeo.org/postgis/ticket/3490

To my disappointment, I noticed our spatial indexes no longer worked if I do
this since they rely on SQL inlining.

Schema qualifying our function calls is not an option at this time since

1) People install postgis in different schemas so we'd have to force them to
install in the same schema which I think will break a lot of 3rd party apps.
2) It's a lot of functions to hand touch.

Any suggestions are welcome. Any other issues I should be aware of with

ALTER FUNCTION .. set search_path..

Only other I noticed is it seems to be ignored in CREATE EXTENSION script
(at least when using dynamic execute). I put it in and it seems to be
entirely ignored.

Thanks,
Regina

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-03-05 03:04:03 Re: The plan for FDW-based sharding
Previous Message Joshua D. Drake 2016-03-05 02:05:06 Re: JPUG wants to have a copyright notice on the translated doc