Re: BUG #15122: can't import data if table has a constraint with afunction calling another function - Mailing list pgsql-bugs

On Tue, Mar 20, 2018 at 12:11:45PM +0000, Andrew Gierth wrote:
> >>>>> "Asier" == Asier Lostalé <asier.lostale@openbravo.com> writes:
> 
>  Asier> Thanks Andrew for your quick response and clear explanation.
> 
>  Asier> Can I understand from your explanation this is not considered as
>  Asier> a bug?
> 
> I would call it a misfeature rather than a bug.
> 
>  Asier> Although the adding a qualified reference workarounds the
>  Asier> problem, it forces to write pl code that is aware of the schema
>  Asier> it is going to be imported in. How could I write this code to be
>  Asier> schema agnostic, so I can import it in any schema without
>  Asier> modifying it?
> 
> For plpgsql (and other pl/* languages, but not LANGUAGE SQL) the best
> way is probably to do this:
> 
> SET search_path = public;   -- or whatever schema
> 
> CREATE OR REPLACE FUNCTION is_even_positive(integer)
>   RETURNS boolean
>   LANGUAGE plpgsql
>   IMMUTABLE
>   SET SEARCH_PATH FROM CURRENT    -- ** this is the important bit
>   AS $$
>     begin
>         return is_even($1) and $1 >= 0;
>     end;
> $$;
> 
> Some caveats:
> 
> 1) The default search_path is "$user",public. Using SET SEARCH_PATH FROM
>    CURRENT doesn't interact well with this (arguably this part _is_ a
>    bug), so either ensure that the search_path is set to something that
>    doesn't exclude $user, or (if you need something that works in a
>    script) you can canonicalize it first using this query:
> 
>    SELECT set_config('search_path',
>                      string_agg(quote_ident(s),','),
>                      false)  -- change to true for equivalent of SET LOCAL
>      FROM unnest(current_schemas(false)) s;
> 
> 2) This doesn't work well for LANGUAGE SQL functions since it would
>    block inlining, which is usually the primary reason for using
>    LANGUAGE SQL in the first place. I don't know of any good workaround
>    for those except to explicitly use the schema in the function body
>    (possibly via text substitution).

[Hackers email list added.]

Uh, you might want to look at this thread too:


https://www.postgresql.org/message-id/flat/152106914669.1223.5104148605998271987%40wrigleys.postgresql.org#152106914669.1223.5104148605998271987@wrigleys.postgresql.org

In that thread there is discussion of how function body extensions can
reference other extension objects when the function doesn't know what
schema it is being installed in.  It uses SQL functions in an index, and
it is inlining that is causing the restore failure.

I had forgotten that SET SEARCH_PATH FROM CURRENT can be used during
function creation, but that forces the function to trust the schema it
is being installed in, which we don't want because it opens us up to the
same problems the removal of public was trying to avoid.

So, the crux of the problem is how do you install a function in an
non-predefined schema, e.g. public, and reference other extension
objects in a safe and transparent way.  This is true of non-extension
objects as well if they are assuming they can reference other objects
that were created earlier, and the schema is not predefined.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +