I'm migrating a database from pg of version 13 to 17 one. Schema it self was recreated with the same migration scripts. The data was transferred with (conceptually)
pg_dump -a -U postgres -h pg13 | psql -h pg17 -U postgres
While data migration I've faced with an issue of functions used within the triggers were not being resolved within the search_path. For that point I've just solved the issue disabling related triggers and restored them after the tables populated right in the dump script.
Once the data is migrated in such the way the triggers in the result db working properly for the very same related tables on the very same psql connection attributes.
There's Materialized view within the database which has nested dependencies on some functions (E.g. view -> func_1 -> func_2)
On a schema generation step there was no any issues. (Though I'm not sure should the creation command verify any dependency chains)
Still after the data migration step there's an issue with the refreshing of the view
refresh materialized view my_view;
What is odd it fails resolving the nested dependency it self as func_2 is not resolving within the func_1. Which means the func_1 is resolved well.
What is interesting the call to func_1 on a same psql connection works well as well.
Specifying explicitly search_path as "$user",public for the func_1 declaration it self solves the current issue still it turns to another functions with the very same problem.
search_path properly to make them work in all the cases the same wayThe first of all I should mention that all the entities within the data base are scoped to a public schema only. So in theory it seems just the default search_path="$user", public should work for all the cases
Besides I've tried to set the search_path explicitly through
Thank you!
Ever since commit 3d2aed664e, PostgreSQL restores dumps with search_path set to an empty string, so that only objects from the system schema pg_catalog can be used without schema qualifications. Other operations have been secured similarly since, notably commit c90c84b3f7 fixed the problem for functions used in REFRESH MATERIALIZED VIEW.
The problem is that you were careless in defining your trigger functions and materialized views by having them depend on the current setting of search_path. The more secure setting exposes the bug.
Fix your function definitions by explicitly setting a search_path:
ALTER FUNCTION trigger_fun() SET search_path = some_secure_schema;
If you want to do that for all functions in a schema, and you are using psql, you might be able to do something like
SELECT format(
'ALTER FUNCTION %s SET search_path = ''myschema'';',
f.oid::regprocedure
)
FROM pg_proc AS f
WHERE NOT EXISTS (SELECT FROM pg_depend AS d
WHERE deptype = 'e'
AND f.oid = d.objid
AND d.classid = 'pg_proc'::regclass)
AND f.pronamespace = 'myschema'::regnamespace \gexec