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