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

-----Original Message-----
> From: Andreas Karlsson [mailto:andreas@proxel.se]
> Sent: Tuesday, March 08, 2016 10:43 PM
> To: Regina Obe <lr@pcorp.us>; 'Robert Haas' <robertmhaas@gmail.com>
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?


> Hi,

> I think Robert was asking for a test case for the database restore problems.

> The reason your no_inline() function cannot be inlined is due to lack of support of inlining of any functions which
haveany config variable set, not matter which. The search_path does not get any special treatment, and I am not sure if
it
> could in the general case since the new search path will apply too to functions called by the function which changed
thesearch path. 

> Andreas

Restore has been an issue since as far back as I can remember.  It's more of an issue now now that people are using
materializedviews and raster constraints. 
Anytime you have a materialized view or check constraint on a table that  uses a function that calls a non-schema
qualifiedfunction you have a problem. 

For a simple example lets say you created a database like this:
-- code start here --
CREATE DATABASE test;
ALTER DATABASE test SET search_path = public,funcs;

\connect test;
CREATE SCHEMA 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.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 ;


CREATE MATERIALIZED VIEW vw_bag_boxes AS
SELECT *
FROM bag_boxes
WHERE funcs.inline('((1,2),(3,4))'::box, geom);

-- code end here --


When you back up the database, it would create a backup with this line:

SET search_path = public, pg_catalog;
--your create materialized view here

When you restore even if your database has search_paths set, your materialized view will not come back and will error
outwith: 

ERROR:  function _helper(box, box) does not exist
LINE 2:  SELECT $1 && $2 AND _helper($1,$2) = 0;                            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:SELECT $1 && $2 AND _helper($1,$2) = 0;

In the case of table constraints, if you have any that rely on functions like this, your data fails validation so will
notcome back. 

Ideally it would be nice if pg_dump allowed specifying additional schemas to add to the search_path.

We have a similar issue with Foreign tables, but that's probably a harder one to fix.

Anyway it seems I underestimated the many ways setting search path on functions (even ones that don't rely on anything
elseas far as I can tell) screws up performance 
Even when it doesn't affect index usage so that has to be done with caution I guess.

Thanks,
Regina