I ran into a situation with pg_restore that for all the world looks like a race condition to me. But this being Postgres I just can't believe I'm encountering an actual bug with something so straight forward. Also, my whole DB sync setup has worked for many years now and this is the first time I'm experiencing an issue like that after a recent schema change.
I'm trying to restore a dump of a schema. So the original schema gets dropped and we have a fresh start. The whole sync of multiple gigabyes works perfectly except for one table, that get created but doesn't receive it's data.
An error in the log informs me that the function clean_text_for_search(text) does not exist:
pg_restore: error: COPY failed for table "am_locations": ERROR: function clean_text_for_search(text) does not exist
The same log, two minutes before the error, also states:
pg_restore: creating FUNCTION "public.clean_text_for_search(text)", with no error.
When, after the restore, I'm trying to create a row in the am_locations table with exactly the same data that cause the error during restore, it works perfectly.
The function in question (clean_text_for_search) gets called by another function zip_code_to_tsvector which in turn is called as part of a generated column of am_locations. Copying a row of data (presumably the first one) from the dump into the table triggers the error.
My current wild guess is, that there is some function execution scheduler that can't deal with second level dependencies. But that is wild speculation and seems very implausible.
Does anyone has an idea what's going on here?
Note the difference: The error message complains that the function clean_text_for_search(text) does not exist, while the earlier log entry confirms that the function "public.clean_text_for_search(text)" has been created. The latter is schema-qualified, the former is not.
pg_restore operates with an empty search_path. So nested function names have to be schema-qualified. See:
The nesting function zip_code_to_tsvector() could set its own search path. But the clean solution is to schema-qualify the function name in the function body of zip_code_to_tsvector() (and all other uses). So public.clean_text_for_search(...) instead of just clean_text_for_search(...) - which is never a bad idea to begin with (unless you deliberately want to depend on the search_path setting).