In PostgreSQL using only public schema, data in tables that use in check constraints functions that invoke other functions does not get imported with pg_restore after it was dumped with pg_dump. But if functions in check constraints do not invoke other functions, data is correctly imported.
I have noted this behavior after minor upgrading from 9.3.22 to 9.3.23 and from 9.4.16 to 9.4.17; in 9.3.22 and 9.4.17 it worked fine.
I understand is due to changes in search_path:
Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane)
pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided applications were themselves vulnerable to the type of hijacking described in the previous changelog entry; since these applications are commonly run by superusers, they present particularly attractive targets. To make them secure whether or not the installation as a whole has been secured, modify them to include only the pg_catalog schema in their search_path settings. Autovacuum worker processes now do the same, as well.
In cases where user-provided functions are indirectly executed by these programs — for example, user-provided functions in index expressions — the tighter search_path may result in errors, which will need to be corrected by adjusting those user-provided functions to not assume anything about what search path they are invoked under. That has always been good practice, but now it will be necessary for correct behavior. (CVE-2018-1058 or CVE-2018-1058)
Still it's unclear to me why having one level public functions is allowed but it is not those functions to invoke other ones.
For example, having this structure and data:
CREATE OR REPLACE FUNCTION is_even(n integer) RETURNS boolean AS $BODY$
BEGIN
return n%2 = 0;
END ; $BODY$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION is_even_positive(n integer) RETURNS boolean AS $BODY$
BEGIN
return is_even(n) and n > 0;
END ; $BODY$ LANGUAGE plpgsql;
CREATE TABLE test_check (
n integer
CONSTRAINT even_chk CHECK (is_even(n)));
CREATE TABLE test_check2(
n integer
CONSTRAINT even_positive_chk CHECK (is_even_positive(n)));
insert into test_check values (2);
insert into test_check values (-2);
insert into test_check2 values (2);
Exporting it with:
pg_dump -h localhost -p 5432 -F c -b -v -f test.dmp test -U test
And importing it in a new database:
$ pg_restore -d test2 -U test -v test.dmp -h localhost
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: creating FUNCTION "public.is_even(integer)"
pg_restore: creating FUNCTION "public.is_even_positive(integer)"
pg_restore: creating TABLE "public.test_check"
pg_restore: creating TABLE "public.test_check2"
pg_restore: processing data for table "public.test_check"
pg_restore: processing data for table "public.test_check2"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2035; 0 7784774 TABLE DATA test_check2 tad
pg_restore: [archiver (db)] COPY failed for table "test_check2": ERROR: function is_even(integer) does not exist
LINE 1: SELECT is_even(n) and n > 0
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT is_even(n) and n > 0
CONTEXT: PL/pgSQL function public.is_even_positive(integer) line 3 at RETURN
COPY test_check2, line 1: "2"
pg_restore: creating ACL "SCHEMA public"
WARNING: errors ignored on restore: 1
Note test_check table gets correctly imported with data whereas test_check2 fails.
The solution is to explicitly set a search_path for the functions.
Then it won't matter that pg_restore sets search_path = pg_catalog, bacause it will be overridden by the function's setting.
This will also protect the functions from inadvertedly picking up functions and operators from a different schema that happens to be set in the calling session (which is the security problem the change is trying to fix).
ALTER FUNCTION is_even_(integer) SET search_path=public;
ALTER FUNCTION is_even_positive(integer) SET search_path=public;