I have a procedural function (written in pl/python) which queries table A, does some calculations and then returns a set. I use this function as query for my materialized view B. Everything works perfectly except that when I want to restore my dump, I get the following error:
DETAIL: spiexceptions.UndefinedTable: relation "A" does not exist.
The line which raises this error is the last line of my sql dump:
REFRESH MATERIALIZED VIEW B;
I know that I can ignore this error and refresh my materialized view after restoration process, but I want to know why this happens? Is it because this function runs in another transaction which doesn't know anything about current restoration process? And what can I do to prevent this error?
For security reasons, pg_dump (or pg_restore) emits a command which empties the search_path, so when you restore the process gets run with an empty search path. But it does not edit the text body of your function at all but emits it as-is, so it can't alter it to specify the fully qualified name of the table. So the function can't find the table when run inside the process doing the restore.
You can fully qualify the table name in the function, or you can define the function with SET search_path = public. Or you can edit the dump file to remove the part that clears the search_path, if you are not concerned about the security implications.