pg_restore fails when trying to create function referencing table that does not exist yet

Question

I've used pg_dump --no-privileges --format custom --compress=0 some_database > my-dump.pgdump to dump a database, but I'm running into issues when I try to restore it.

Specifically, it appears to be loading function definitions before table definitions:

$ pg_restore ./my-dump.pgdump
…

create function my_function() returns …
language sql $$
  select …
  from some_table
  where …
$$;

… later in the dump …

create table some_table ( … );

…

Which causes an error when I try to restore the dump:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4863; 0 16735 TABLE DATA some_table some_database
pg_restore: [archiver (db)] COPY failed for table "some_table": ERROR:  relation "some_table" does not exist
LINE 3:                     from some_table
                                 ^
QUERY:
                    select …
                    from some_table
                    where …

CONTEXT:  SQL function "my_function" during inlining

What's going on here? How can I trick pg_dump / pg_restore into doing things in the correct order?

Answer 1

Check your dump file for commands which mess with search_path, for example:

SELECT pg_catalog.set_config('search_path', '', false);

I encountered the same kind error as you (relation xxx does not exist ... during inlining) in a legacy project I inherited, even though it's running PostgreSQL 9.4.x.

I traced it to the above command.

The solution for me was to remove this command from the dump file.

After I did this I was able to restore the database without errors.