From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Harry Green <harrygreen91@yahoo.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
Date: Wed, 20 Sep 2023 11:16:26 -0700
Message-ID: <8a0ee0de-57c7-eebd-0e9f-691e04ab2999@aklaver.com> (raw)
In-Reply-To: <1814194178.6529176.1695232793334@mail.yahoo.com>
References: <73791286.4632684.1695049729344.ref@mail.yahoo.com>
<73791286.4632684.1695049729344@mail.yahoo.com>
<11b8ca504cd625d45d561c9a1b69700f1ec5ef55.camel@cybertec.at>
<1814194178.6529176.1695232793334@mail.yahoo.com>
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.
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, laurenz.albe@cybertec.at
Subject: Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
In-Reply-To: <8a0ee0de-57c7-eebd-0e9f-691e04ab2999@aklaver.com>
References: <73791286.4632684.1695049729344.ref@mail.yahoo.com> <73791286.4632684.1695049729344@mail.yahoo.com> <11b8ca504cd625d45d561c9a1b69700f1ec5ef55.camel@cybertec.at> <1814194178.6529176.1695232793334@mail.yahoo.com> <8a0ee0de-57c7-eebd-0e9f-691e04ab2999@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