pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (8+ messages)  latest in thread

Message-ID: <8a0ee0de-57c7-eebd-0e9f-691e04ab2999@aklaver.com>
Permalink:  ../8a0ee0de-57c7-eebd-0e9f-691e04ab2999@aklaver.com/
Also on:    postgresql.org/message-id/8a0ee0de-57c7-eebd-0e9f-691e04ab2999@aklaver.com

 · 

reply

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