Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux