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:16, Adrian Klaver wrote:
> 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.

Forgot to add to above, that if you want to do this sort of thing then
use a trigger. In a dump/restore they are added back to the tables after
the tables and table data have been restored.

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