On Mon, 2023-09-18 at 15:08 +0000, Harry Green wrote:
> I am having trouble restoring a database backed up with pg_dump/pg_dump_all.
> The error messages I get are below and appear to suggest that certain sql-language or
> pl/pgsql-language functions which include an sql statement referencing a table are
> trying to be executed before the table which they reference has been created.
> I am surprised that pg_dump could get the order wrong, but that is what is happening.
>
> Looking at the first of the error messages, the pl/pgsql function is created as
> create function public.check_account_from_bill_items(character...) on line 95,
> but the instruction to create the accounts table '... create table public.accounts ... '
> appears on line 510.
>
> Consequently, the restore does not work because the relations are created in the
> wrong order. Any ideas how I can solve it?
>
> Please see the error message below:
>
> psql:20230913_1300.sql: ERROR: relation "accounts" does not exist
> LINE 1: select name from accounts where (type='Expense' or ...
> ^
> QUERY: select name from accounts where (type='Expense' or type='Asset' or type='Stock') and name=account
> CONTEXT: PL/pgSQL function public.check_account_from_bill_items(character varying) line 6 at SQL statement
> COPY bill_items, line 1: "4096 Website 0 11.6099999999999994 \N \N 0 11.6099999999999994 5852 1 \N"
>
> psql:20230913_1300.sql:201718: ERROR: relation "ebooks" does not exist
> LINE 1: select count(*) from ebooks where isbn13=$1
> ^
> QUERY: select count(*) from ebooks where isbn13=$1
> CONTEXT: SQL function "ebook_records_with_isbn13" during inlining
> COPY book_information, line 1: "Title abcd \N \N 1-2222-2222-1 111-1-2222-2222-1 11.11 111.11 1 \N ..."
>
> [...]
>
> psql:20230913_1300.sql:20459125: ERROR: insert or update on table "book_praise" violates foreign key constraint
"book_praise_title_fkey"
> DETAIL: Key (title, binding, isbn)=(XXCCC, back, 1111-55) is not present in table "book_information".
>
> psql:20230913_1300.sql:20459381: ERROR: insert or update on table "posters" violates foreign key constraint
"posters_isbn_fkey"
> DETAIL: Key (isbn)=(1-3333-3333-3) is not present in table "book_information".
>
> [...]
It seems like you are restoring the dump in the wrong way.
pg_dump dumps foreign key constraints last of all, so during a restore they are created
after all the data have been inserted into the tables, and the error you see can never
happen. You must be restoring a data-only dump into an already existing schema with
foreign keys in place. That won't work and isn't supported. Don't forget that foreign
keys can be circular, and there might be no "correct order" to dump the tables.
The first errors with the function calls are less obvious, but existing triggers on the
tables micht be an explanation. Since one of the functions is called "check_...",
another explanation could be that you have check constraints that use functions that
access other tables. That won't work and is not allowed.
Without knowing more, I cannot be certain what exactly is wrong, but it doesn't look
like a PostgreSQL bug to me. Perhaps you can provide more details.
Yours,
Laurenz Albe