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

On 9/20/23 10:59 AM, Harry Green wrote:
> Thank you Laurenz for your answer,
> I have done a complete pg_dump and complete restore, and tried all
> forms of complete pg_dumps and restores (i.e. the different formats).
> There is no partial or tables only or data only dumping or restoring.
> Further, you refer to a 'check constraint' but this is just a function
> that we wrote with the 'create function...' command, whose name if
> memory serves correctly happens to begin by 'check...' because that
> happens to be the most descriptive word for what it does.
>
> The pg_restore is actually correctly recreating the function using the
> 'create function...' command, but it is doing so on line 95, ahead of
> the 'create table ... accounts ...' which appears on line 510. The
> trouble is that in the function itself, we refer to the accounts
> table, and if I am not mistaken, the whole point of pl/pgsql is that
> you can refer to tables, etc. so I don't think I have done anything
> 'illegal'.

The issue is not that a table name is being used in a function, it is
that the function is

being used before the table is created.

My suspicion still is that it is being used as a CHECK function.

I would suggest taking a schema only dump of the database e.g:

pg_dump -d <db_name> -s -f db_name_schema.sql

This will dump only the schema(object) definitions. Then search that
file for

check_account_from_bill_items. I'm betting it is used somewhere before
the accounts table

is created.