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.klaver@aklaver.com