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

-----Original Message-----
> From: Andreas Karlsson [mailto:andreas(at)proxel(dot)se]
> Sent: Tuesday, March 08, 2016 10:43 PM
> To: Regina Obe <lr(at)pcorp(dot)us>; 'Robert Haas' <robertmhaas(at)gmail(dot)com>
> Cc: pgsql-hackers(at)postgresql(dot)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 have any 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 the search 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 materialized views and raster constraints.
Anytime you have a materialized view or check constraint on a table that uses a function that calls a non-schema qualified function 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 out with:

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 not come 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 else as 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