Re: BUG #15122: can't import data if table has a constraint with a function calling another function

Thanks Andrew for your quick response and clear explanation.

Can I understand from your explanation this is not considered as a bug?

Although the adding a qualified reference workarounds the problem, it
forces to write pl code that is aware of the schema it is going to be
imported in. How could I write this code to be schema agnostic, so I can
import it in any schema without modifying it?

On Tue, Mar 20, 2018 at 11:13 AM, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk
> wrote:

> >>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
>
> PG> Using only public schema, data in tables that use in check
> PG> constraints functions that invoke other functions does not get
> PG> imported with pg_restore after it was dumped with pg_dump. But if
> PG> functions in check constraints do not invoke other functions, data
> PG> is correctly imported.
>
> PG> I have noted this behavior after minor upgrading from 9.3.22 to
> PG> 9.3.23 and from 9.4.16 to 9.4.17; in 9.3.22 and 9.3.17 it worked
> PG> fine.
>
> This is definitely fallout from the security fixes related to
> search_path.
>
> PG> But it's unclear to me why having one level public functions is
> PG> allowed but it is not those functions to invoke other ones. It
> PG> looks inconsistent.
>
> Yes, the reason for this is that function bodies are for the most part
> treated as opaque strings everywhere except when actually executing the
> function. This means that when a function makes a non-schema-qualified
> reference to another function in its body, the search_path lookup is
> performed at runtime, and so it depends on the runtime setting of
> search_path.
>
> In contrast, a CHECK constraint stores a pre-parsed expression tree
> which refers to the function by its oid, not name, so that when pg_dump
> dumps it out as SQL it can use a schema-qualified name in the output.
>
> Since pg_dump now does the restore with only pg_catalog in the
> search_path, the first function is successfully called because it is
> schema-qualified in the CHECK constraint definition, but the second
> function is not found because it is referenced only by an unqualified
> name. You could do this:
>
> PG> return is_even(n) and n > 0;
>
> return public.is_even(n) and n > 0;
>
> --
> Andrew (irc:RhodiumToad)
>