From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Harry Green <harrygreen91@yahoo.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
Date: Mon, 18 Sep 2023 08:16:14 -0700
Message-ID: <015fcbcb-774a-a1e9-0c73-ed93a1f9e30a@aklaver.com> (raw)
In-Reply-To: <73791286.4632684.1695049729344@mail.yahoo.com>
References: <73791286.4632684.1695049729344.ref@mail.yahoo.com>
<73791286.4632684.1695049729344@mail.yahoo.com>
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.
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, harrygreen91@yahoo.com
Subject: Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
In-Reply-To: <015fcbcb-774a-a1e9-0c73-ed93a1f9e30a@aklaver.com>
References: <73791286.4632684.1695049729344.ref@mail.yahoo.com> <73791286.4632684.1695049729344@mail.yahoo.com> <015fcbcb-774a-a1e9-0c73-ed93a1f9e30a@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox