Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qj1ka-008zaa-Dl for pgsql-general@arkaria.postgresql.org; Wed, 20 Sep 2023 18:16:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qj1kY-0022rs-Vt for pgsql-general@arkaria.postgresql.org; Wed, 20 Sep 2023 18:16:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qj1kY-0022rI-0N for pgsql-general@lists.postgresql.org; Wed, 20 Sep 2023 18:16:34 +0000 Received: from wout4-smtp.messagingengine.com ([64.147.123.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qj1kV-005fTJ-5Z for pgsql-general@lists.postgresql.org; Wed, 20 Sep 2023 18:16:32 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.west.internal (Postfix) with ESMTP id 6E7233200063; Wed, 20 Sep 2023 14:16:29 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Wed, 20 Sep 2023 14:16:29 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to; s=fm1; t=1695233788; x=1695320188; bh=uK hENA3vgoXnbDce60f2JgmXfpiYaRWFgxrKxoKpO5A=; b=qdpe9E6IWwrtaXBL5i 49Vr5N+WSNtZ02r87gL4nYseqJg4B0avTrF3QqHybxpsH5QgbVugXez9H9O/318Z TzhWkBQ974nZ8pUPVmxNCPW4bEl01Jrd2JIrvqO0nyxZu+d7ERKH+fbvUJvWtTwb HEjyqWLoeV3g67TsDBwjf2Akju81n9Fkb6/Q1MG7S7K4QU/CEJDQ5bepUoqBOO+P ej2xesa/B0SwSHyfCCTG3tNaMh2K48+dsHzTec94ZaiM7fe+I1B8xnNPlyKbNxA2 T5yhfCgiE2AStP/At0DLF2Svll5gb9HBN6TfsiSeeCN+IkNRVaP/a9RIhYS3RHeA x++Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; t=1695233788; x=1695320188; bh=uKhENA3vgoXnb Dce60f2JgmXfpiYaRWFgxrKxoKpO5A=; b=YeWqSC4bCY24ltGVWaW5nqRy50llI vV69WGM5+CwcYsf20jby+vO9ORu6dnkG9S6LNjpkBhCtmErda4K0zbKoLe5YD3jj cWoajqL6ckwzfInMYUMEouJ8KEj/hp0PX5BvESkFFw1wdbc2KE4UT4wNoPwSY7cQ dZMJeoqWVwSwMcyukGr1caQuIX9m+7fglGYamf62fDcfq74nNlJc7rfo/DRAFVL6 tNDu9BsoKXgyh0EAQ7uaz3icy4aZNCrScNoIhdsSsXiDeT0sZ7//yQ5EyAWJwICw 2MHLqLdMoxXDv351hHO93/RHl5ZhYfjwdakRvKpFDSmCHHCZOdLdeBpfw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrudekfedguddvhecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpegtkfffgggfuffvfhfhjgesrgdtreertdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeeiveehkefhgffgfeeghfehudelieehtdejgeehvdej jeejjeelgefhfeekudfhgfenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 20 Sep 2023 14:16:28 -0400 (EDT) Content-Type: multipart/alternative; boundary="------------ZWS6gyAEb1l00qRu0Vf1ghbA" Message-ID: <8a0ee0de-57c7-eebd-0e9f-691e04ab2999@aklaver.com> Date: Wed, 20 Sep 2023 11:16:26 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.1 Subject: Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Content-Language: en-US To: Harry Green , "pgsql-general@lists.postgresql.org" , Laurenz Albe References: <73791286.4632684.1695049729344.ref@mail.yahoo.com> <73791286.4632684.1695049729344@mail.yahoo.com> <11b8ca504cd625d45d561c9a1b69700f1ec5ef55.camel@cybertec.at> <1814194178.6529176.1695232793334@mail.yahoo.com> From: Adrian Klaver In-Reply-To: <1814194178.6529176.1695232793334@mail.yahoo.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ZWS6gyAEb1l00qRu0Vf1ghbA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 -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. --------------ZWS6gyAEb1l00qRu0Vf1ghbA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


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.


--------------ZWS6gyAEb1l00qRu0Vf1ghbA--