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?

Seashore

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.

Furl

Errors of that kind point to insecure function definitions. It is better to fix the original function. – Allrud

There appears to be a security related reason why the set search_path was done in the dump: postgresql.org/message-id/… – Superposition

OP's dump format is custom, so you can't edit that kind of dump file. – Superposition

I basically had to prefix the table name in the check query with the schema name, e.g. public.users instead of users. Worked like a charm right after. – Fibrinous

Answer 2

Note: the OP is using the custom format. There is no editing the binary file that is emitted.

In my experience, pg_dump using the custom format (-Fc) doesn't set check_function_bodies = false. But since it adds random functions at the top of the dump file (instead of putting all routines at the end), this causes pg_restore to barf.

I was able to workaround this issue by setting PGOPTIONS:

export PGOPTIONS="-c check_function_bodies=false"
pg_restore ...

Superposition

Answer 3

That is strange. Ever since commit ef88199f611e625b496ff92aa17a447d254b9796 in 2003, pg_dump and pg_restore have emitted

SET check_function_bodies = false;

This setting makes sure that an error like you describe won't happen, because PostgreSQL won't check the validity of the function bodies.

Are you using an ancient PostgreSQL version or are you doing anything else that could mess with that?

If you run pg_restore on your dump (without specifying a destination database), does it emit the line?

Allrud

What is silly here is that the -s param considers routines "schema". You should be able to restore only the table definitions, and then the functions. Or better, pg_restore should know this is an issue and make two passes on the dump file to first do tables only. Or even better still, pg_dump should dump routines last. – Superposition

@Superposition Functions are certainly part of the object definitions (schema). You are suggesting a feature to selectively restore functions or not, but that seems to have little to do with the question. – Allrud

While routines are technically DDL, I argue they are not database structure, and certainly not data, the two things people primarily care about when restoring a dump. Yes, you need all bits and pieces eventually, but you need to be able to easily restore just structure and data (including composite types referenced by table columns). pg_restore seems hell bent to make just restoring structure and data as hard as possible, IMO. – Superposition

@Superposition I disagree. Are views part of what xou call data? Are constraints? Both can use functions. Unless your objects are ill-defined, there is no problem restoring a dump. – Allrud

Tables and data. Make it simple to do just those two (with constraints). Everything else is secondary when you're just trying to move data. – Superposition

@Superposition I see. Still, I'd say that that is a minority viewpoint. – Allrud

Sorry @Laurenz. I keep trying to mention you but it's not working for me. I agree moving just data isn't a primary case, it is a principle case for development environments. I'm hot on this issue because I got burned by it just trying to copy a bunch of data out of our prod data lake into our dev lake where we just needed stuff to play with. When you're moving data into a db with other developers, you can't blow away their routines and other stuff. You have to be surgical. – Superposition

@Superposition If you want to do that, perhaps keep tables and functions in different schemas. – Allrud

Great idea @Laurenz. Thanks so much! – Superposition

Answer 4

I'm 5 years late but here's the answer: it's a "known" error. You can read more about it from Bruce Momjian. It's because you're using a "pure" SQL function and those are not being ignored by check_function_bodies=false.

In my case I needed to revisit my database structure before I could upgrade from pg15 to pg16.

Esotropia