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 1qiFz9-005kvc-8g for pgsql-general@arkaria.postgresql.org; Mon, 18 Sep 2023 15:16:27 +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 1qiFz7-001pXy-NR for pgsql-general@arkaria.postgresql.org; Mon, 18 Sep 2023 15:16:25 +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 1qiFz6-001pXc-M8 for pgsql-general@lists.postgresql.org; Mon, 18 Sep 2023 15:16:25 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qiFyz-005H3L-DG for pgsql-general@lists.postgresql.org; Mon, 18 Sep 2023 15:16:23 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id 12E275C015E; Mon, 18 Sep 2023 11:16:16 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Mon, 18 Sep 2023 11:16:16 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding: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= 1695050176; x=1695136576; bh=QsYj+QOdMa2kUJar3/UtVb/3DjwQht+pf1U kJTf1+uo=; b=qIs7nDKWobxDbLTCvrs5ppGJymabItx+K/nVIRnxvlfjUAHVD2T h4+wZ/Q6bJ4ZqN8LXTnmtTJYP2fvcLHY07rDYL4AMSBukw6WOUDMaKaY3IYz1q6i 9rGELcQeWL5CydDLuXMZTrqcUN9aiUhsJZkxS8Wt36aT2eQleUAyJ6hcztk3y7fd QPCVa5l7MwBcm2XNEpLXKHi9I39wgatHmah7usLX8EPT12BQI864EZz9MYvdajOj 9TUIM6IglMuVOff/cu8z9kbkpfSfHMTO+WyLq4FHUTFqSncDED4T9Qwp98Fgd3Js Sb8Ty2ggnT4PjFsARoj8xTC7qzAfgsioA4Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding: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=1695050176; x= 1695136576; bh=QsYj+QOdMa2kUJar3/UtVb/3DjwQht+pf1UkJTf1+uo=; b=D EAjCQoplE4fIw/yq27/Fmg3tEtcWgrGAyCSjwy4OrNfSYcRbob3dYA+wiKyxhrub yLKRpVpm1bQYmuIg0HxWdJ4q8MdUy1yElnVtRV8ly/aYIwVqfEV5gcp79YCqrtxd 670S+sNTjm9CP5Rei1OaC/C6F1DbuThTTox+HLT8GhzRZVpSk8tyzYsGwMKHAi/w dMFIgHOVF6AnlHcz6CIRTmiKw5gLpeEcEdUm22tOXxPduz5oMzGqGlRHgoi/x0br jqgV5+yHvIiThK1dgU81C3aktiqmZD0gKf2KODPeGBGIshCKpzqE1D3aPNSjiI0z /0dH56GVY7cuYUB/2UWqA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrudejkedgkeegucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeelkeegjefhkeetheefudevteehiedvudehkeduleet fedvffeuhedtvdegtdeffeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 18 Sep 2023 11:16:15 -0400 (EDT) Message-ID: <015fcbcb-774a-a1e9-0c73-ed93a1f9e30a@aklaver.com> Date: Mon, 18 Sep 2023 08:16:14 -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 To: Harry Green , "pgsql-general@lists.postgresql.org" References: <73791286.4632684.1695049729344.ref@mail.yahoo.com> <73791286.4632684.1695049729344@mail.yahoo.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: <73791286.4632684.1695049729344@mail.yahoo.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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