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

>>>>> "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)