Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order

On 9/18/23 08:08, Harry Green wrote:
> Hello,
>
> 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.

I'm betting this a function being used in a table CHECK and per:

https://www.postgresql.org/docs/current/sql-createtable.html

"Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row (see Section 5.4.1). The
system column tableoid may be referenced, but not any other system column."

There is no dependency checking for CHECK functions.

>
> 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:

> Thanks a lot!
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com