-----Original Message-----
> From: Andreas Karlsson [mailto:andr...@proxel.se] 
> Sent: Tuesday, March 08, 2016 10:43 PM
> To: Regina Obe <l...@pcorp.us>; 'Robert Haas' <robertmh...@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 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 --
  SET search_path = public,funcs;

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

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.
 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 
Even when it doesn't affect index usage so that has to be done with caution I 


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to