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 1qj1Uc-008yen-2N for pgsql-general@arkaria.postgresql.org; Wed, 20 Sep 2023 18:00:06 +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 1qj1Ua-001gON-Nq for pgsql-general@arkaria.postgresql.org; Wed, 20 Sep 2023 18:00:04 +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 1qj1UZ-001gO1-VS for pgsql-general@lists.postgresql.org; Wed, 20 Sep 2023 18:00:04 +0000 Received: from sonic311-31.consmr.mail.ir2.yahoo.com ([77.238.176.163]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qj1UW-005fLm-BO for pgsql-general@lists.postgresql.org; Wed, 20 Sep 2023 18:00:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1695232796; bh=fbZaeBV29sL/lNFqKXwRqMq588QFQVd4uHLbgwe1cN0=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=EegH7R+BjAhlXBFUdwHf0iIAsGN8XnlKi+7SAwZtyDgb/6zs1w44NL+ofTRla6z+KhnQRZyDM9fVlkJ0ccpW1uzczkxc4HuviduHtKUg0iADRp64mDX+pdvTCYTckRxSjJ6eXC7+urahhMBLqyJ02u6ZBV621qU0o6UVoUUKhd66pSp+iVBR5hBvLeQpefyz3bEr+R/FnawePndbYbykmSxOxs8kCIrqVOny1d2wpzKv21s5Y9DewXL46z8fjQBlHpAt/ObtFutNJRgHKyMpyCbR9Zl/5Mr3bC44oqYe1d/CpMkBwFSBr40ROjoK4MeYBxeHKFcqFsXPIQCvEwFm5g== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1695232796; bh=KDy9jI4r2jmBKIP6+5SWsyLHodyvA2n+B2Qw+td3O4d=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=lhEGD4Y3s4ZflXo6yI3o2UYINnjP0f2sWL7ubJwoMT+8tqwhF1hV38X6Z2vfwOhJDL+xN6B8qyOvLKghp3iVLw1MJEzw0CxL2eicbdm9NNwR83yL4Ij1IoDfXSw7dCYXtp9MQXQ+V65VfZHlkboPBkqw1GGP41xXWPC8vuzhhTPSFucqXjx+WEjhtT0Z5DSM/K/G7k/FtHzCQDvay8bTOx4Y1kG6QL+K1ixIExOOSn2y/+GSws8H2KFOjk9KlayWZGui6BKlo9LDcnQGU+kAlzs5K5rX4NacqoX9r5HuzWnRtadT7W9474wfLXDveMNqljABvitlehanH7C63kkPUQ== X-YMail-OSG: WDDvvDMVM1lGP2u9_OTpjOb4AQC8QvSl0l6vjxx6XWEhQ7L4tp9aOFX6iBREjoc 30uBIvFp.isP5FBVWAI7OWVZF3Si6Gd2v_zCOx3z2VuB6y_wkjRPPSFUlsjXoR.bH1sMjUA8PkW0 CVDxRXeQ0xewQYCh_0r0kyy5mkXvc7U89JnIdh4Y4sTCb7GXybBfw9QhSgw2657mz8XXrj9dIyj. KaOKWDiRovByUFn83fOAd.qKT8qoSqrxXg3rYVA6hG73vsS_ngzxSPMDFY2wZg.qhxyqzX1hQtNS ehPIopqPqOcs5vrZdRDKJljdBAB.L3doKgIGHr9mz7Fzu1t1q9hkD3fsCLGQb.PSEFJtoR0XaFtn 6aY7nH9ILwiPqysNkEqqn_aVS2mZwy.juciF1Qx8fuDwsypbgvvRJZOHsX7KFmlsOMa_xdj0xz2K 6du23.s1lXDxQ_Mq0TENUYZ3MrSSYt.FSC_G93lwGTZ1E1Dpp7RoI3gfnbbJzkSMwiec2O_hSkHl Q6UJ8.qjs9aZBLP7UIPA0ykiB7bJC4_aDyCNPGCAKyu0M80kDMWkW2kbxhtk19vyq2nG6.ukec6T GUqmzuBvDwwmfpoysqPZdwDnZvUSs_1t39Emm17N6Az7gx3Wl6AAV1u_zC_c2oUJ9EMbM_KZiIyQ yOAcAJS_QOv8soGe7EkTtbKtfE1qUdmMIc.5siBzRcAH_nXalt2Jtks202zt.dHKFLetGMkT_kqm ifxl6CxNZQ0sHvqyEvKajLLNGkggSjDHKiDSY4P1v56BQWovxLA_rtp2xx77AzgGMAayh4vliyBf YqgnkKDqcRVs9dWl8GvbriGpljL.Lg_KbBmZ7GLs3giEq396Jxwt_a.3bTvxqlkt9.W6hKnQpTbi affWyMG67crWA2gsZfF6ft3dcIfy_uZuSBNCJZwl0stiGzevm9UFjk47OY3RVO47XkqHnEBKHQLA bc8hwKz5gROcC4_044gyx8I6HeeQ5N_ZpQ.sgQ.waeR_bjregPU4kvd0kzi0VeDzyAEHRUSgpQet 1k3io54NRH2LHL_noJZ.dJUP4lysM4xe4c2kKl5QZZnW4H6O.WhTzCo0kCSL5bOeGcjSK8gNBx5x 6GAx3fGdJEc6rkymJ1S9p0xLAdcQA.AEPpRwgvbnKKHAElu14h7ExGFXFqza..d7xa_z4QJYFG6S Iw2rw0W9tEzz21Sodnz1xxCvC4YkhLdiJkrguxq1WZemT8rWmcjQnig_Rz.l7vQRzMrh6eJtO.X0 MZqo7NsRsT6fOfITGGvZ3Bw_5YsECaQtLjdMsk4.EsIfznTPOnLDVcdSz2rUIPK2Y9SflnJvvx0. 0IZ3HW2UzeXrk9PkVyZn.qrGivk7TjDymuCBOiBujALQvhWiaO12vbWe5eEUDAZXMuvs_QTe4wXn hBaZOsmHgU6UtsmkuLRTe34d8AStQc.WgAHNvyCDJ7AB67VXB5l9FwnUC19g5eyQD5ziJU2UuNXC zO9pbvfM1.a92IMslq9O2xLL9T0EFYuxermKDhZ80FFUsASZk5Jho6QUdHrOEFX5GV72fKouEe4Y Lotfo8q0VifytbJDNftB7aOFia8cAHmKyo6wY.nR_dgDxt0YhY..sgGrpPzxE6QWCnyAPRSGe65p sIhPH_K1e84nZNOpBSqc_4KOS_FPuGu3L0g4i2AP0ApPFTcuziz8qzA_gdOBPINJr0TV4RKeDMCm 1M9BlbZyFbBqpXs3PkrFej30jEjj6KLm9Yccf74tsFeBS8zxYrIyBqRgv00L5dOcC_wl7L0UdfBh N5FkS5.scMhFeLkrki1KGxWHgGVDQdluo5l84vPhHNUlTcD2JKhV1HIM.uXGkcP7wwkDxSgkaX7I oulIOSotCgKb672Bpt4ugP8nfmFHB6DDughWtzGpDNbzpf5q6BdB4mfEeo3ZOc5E7HWWQ5LpyiXT T5nL.b42Ll5c57jBigDtwnkCXNSup38wIms1Zvws4LoKXuFhF.AB_BAWZmeuO_bLzUOn._Ui8NNq QKyzPIbt2P24Dfw6klU3hLmSwTZhY.ocQg8EHMwoUKa9oFQiwJKBrgWUD4o9eWbRWeq3QGC5.Aal dR9aa757JhIPyNXRyld4adJd73pXeiTKzeVsBj2m2xs9HEMbMEvyWgcRL5Eqd6BHLmzX3NN49V.w LOCdjTjQfhcjM83ZzqW3RDkpBg2EK18k- X-Sonic-MF: X-Sonic-ID: 3882caa9-b9af-4c69-bb1a-61c4f0ddcb69 Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.ir2.yahoo.com with HTTP; Wed, 20 Sep 2023 17:59:56 +0000 Date: Wed, 20 Sep 2023 17:59:53 +0000 (UTC) From: Harry Green To: "pgsql-general@lists.postgresql.org" , Laurenz Albe Message-ID: <1814194178.6529176.1695232793334@mail.yahoo.com> In-Reply-To: <11b8ca504cd625d45d561c9a1b69700f1ec5ef55.camel@cybertec.at> References: <73791286.4632684.1695049729344.ref@mail.yahoo.com> <73791286.4632684.1695049729344@mail.yahoo.com> <11b8ca504cd625d45d561c9a1b69700f1ec5ef55.camel@cybertec.at> Subject: Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6529175_2109166727.1695232793332" X-Mailer: WebService/1.1.21797 YMailNorrin Content-Length: 17421 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_6529175_2109166727.1695232793332 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thank you Laurenz for your answer,I have done a complete pg_dump and compl= ete restore, and tried all forms of complete pg_dumps and restores (i.e. th= e different formats). There is no partial or tables only or data only dumpi= ng or restoring. Further, you refer to a 'check constraint' but this is jus= t a function that we wrote with the 'create function...' command, whose nam= e if memory serves correctly happens to begin by 'check...' because that ha= ppens to be the most descriptive word for what it does.=C2=A0 The pg_restore is actually correctly recreating the function using the 'cre= ate function...' command, but it is doing so on line 95, ahead of the 'crea= te table ... accounts ...' which appears on line 510. The trouble is that i= n the function itself, we refer to the accounts table, and if I am not mist= aken, the whole point of pl/pgsql is that you can refer to tables, etc. so = I don't think I have done anything 'illegal'. I attach the text of the entire create function instruction, and also of th= e create table instruction.=C2=A0 CREATE FUNCTION public.check_account_from_bill_items(id integer) RETURNS b= oolean=C2=A0 =C2=A0 LANGUAGE plpgsql=C2=A0 =C2=A0 AS $$DECLAREexp varchar; BEGINselect into exp account from bill_items where account in (select name = from accounts where type=3D'Expense' or type=3D'Asset' or type=3D'Stock') a= nd bill_items.id=3Did;if exp is not null thenreturn true;elsereturn false;e= nd if; END;$$; ------------------------------------------- CREATE TABLE public.accounts (=C2=A0 =C2=A0 name character varying NOT NULL= ,=C2=A0 =C2=A0 type character varying NOT NULL,=C2=A0 =C2=A0 vat real,=C2= =A0 =C2=A0 account_name character varying,=C2=A0 =C2=A0 account_number char= acter varying,=C2=A0 =C2=A0 sorting_code character varying,=C2=A0 =C2=A0 se= curity_code character varying,=C2=A0 =C2=A0 website character varying,=C2= =A0 =C2=A0 email character varying,=C2=A0 =C2=A0 username character varying= ,=C2=A0 =C2=A0 password character varying,=C2=A0 =C2=A0 institution charact= er varying,=C2=A0 =C2=A0 address text,=C2=A0 =C2=A0 telephone character var= ying,=C2=A0 =C2=A0 description text,=C2=A0 =C2=A0 security_no_for_telephone= character varying,=C2=A0 =C2=A0 id integer NOT NULL,=C2=A0 =C2=A0 subtype = character varying,=C2=A0 =C2=A0 jnbp numeric(3,2) DEFAULT 0.5,=C2=A0 =C2=A0= currency character(3) DEFAULT 'GBP'::bpchar,=C2=A0 =C2=A0 foreign_currency= character(3),=C2=A0 =C2=A0 stopped_using date,=C2=A0 =C2=A0 vat_purchase b= oolean DEFAULT false,=C2=A0 =C2=A0 vat_income boolean DEFAULT false,=C2=A0 = =C2=A0 balance_sheet_type character varying); ALTER TABLE public.accounts=C2=A0 Looking at the documentation for Create Function, I'm not entirely sure whe= ther there is anything in there which is not supposed to be there. At any r= ate, the system does not seem to allow the creation of functions that are '= illegal' for the most part, and I cannot see why anything in here WOULD be = illegal.=C2=A0We are working with version 10.23, and I cannot send you the = entire output of the pg_dump file because it is 3.3 GB, but am happy to sen= d you any parts that might help.=C2=A0 Many thanks for you help - any further suggestions would be great! On Monday, 18 September 2023 at 20:29:19 BST, Laurenz Albe wrote: =20 =20 On Mon, 2023-09-18 at 15:08 +0000, Harry Green wrote: > I am having trouble restoring a database backed up with pg_dump/pg_dump_a= ll. > 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 crea= ted. > I am surprised that pg_dump could get the order wrong, but that is what i= s happening.=C2=A0 >=20 > Looking at the first of the error messages, the pl/pgsql function is crea= ted as > =C2=A0 create=C2=A0 function public.check_account_from_bill_items(charact= er...)=C2=A0on line 95, > but the instruction to create the accounts table=C2=A0 '... create table = public.accounts ... ' > appears on line 510.=C2=A0=C2=A0 >=20 > Consequently, the restore does not work because the relations are created= in the > wrong order. Any ideas how I can solve it? >=20 > Please see the error message below: >=20 > psql:20230913_1300.sql: ERROR:=C2=A0 relation "accounts" does not exist > LINE 1: select=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 name from accounts where= (type=3D'Expense' or ... > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^ > QUERY:=C2=A0 select=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 name from accounts = where (type=3D'Expense' or type=3D'Asset' or type=3D'Stock') and name=3Dacc= ount > CONTEXT:=C2=A0 PL/pgSQL function public.check_account_from_bill_items(cha= racter varying) line 6 at SQL statement > COPY bill_items, line 1: "4096 Website 0 11.6099999999999994 \N \N 0 11.6= 099999999999994 5852 1 \N" >=20 > psql:20230913_1300.sql:201718: ERROR:=C2=A0 relation "ebooks" does not ex= ist > LINE 1: select count(*) from ebooks where isbn13=3D$1=C2=A0 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^ > QUERY:=C2=A0 select count(*) from ebooks where isbn13=3D$1=C2=A0 > CONTEXT:=C2=A0 SQL function "ebook_records_with_isbn13" during inlining > COPY book_information, line 1: "Title abcd \N \N 1-2222-2222-1 111-1-2222= -2222-1 11.11 111.11 1 \N ..." >=20 > [...] > > psql:20230913_1300.sql:20459125: ERROR:=C2=A0 insert or update on table "= book_praise" violates foreign key constraint "book_praise_title_fkey" > DETAIL:=C2=A0 Key (title, binding, isbn)=3D(XXCCC, back, 1111-55) is not = present in table "book_information". >=20 > psql:20230913_1300.sql:20459381: ERROR:=C2=A0 insert or update on table "= posters" violates foreign key constraint "posters_isbn_fkey" > DETAIL:=C2=A0 Key (isbn)=3D(1-3333-3333-3) is not present in table "book_= information". > > [...] It seems like you are restoring the dump in the wrong way. pg_dump dumps foreign key constraints last of all, so during a restore they= are created after all the data have been inserted into the tables, and the error you se= e can never happen.=C2=A0 You must be restoring a data-only dump into an already existi= ng schema with foreign keys in place.=C2=A0 That won't work and isn't supported.=C2=A0 Don= 't forget that foreign keys can be circular, and there might be no "correct order" to dump the tab= les. The first errors with the function calls are less obvious, but existing tri= ggers on the tables micht be an explanation.=C2=A0 Since one of the functions is called = "check_...", another explanation could be that you have check constraints that use funct= ions that access other tables.=C2=A0 That won't work and is not allowed. Without knowing more, I cannot be certain what exactly is wrong, but it doe= sn't look like a PostgreSQL bug to me.=C2=A0 Perhaps you can provide more details. Yours, Laurenz Albe =20 ------=_Part_6529175_2109166727.1695232793332 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thank you Laurenz = for your answer,
I have done a complete pg_dump and complete rest= ore, and tried all forms of complete pg_dumps and restores (i.e. the differ= ent formats). There is no partial or tables only or data only dumping or re= storing. Further, you refer to a 'check constraint' but this is just a func= tion that we wrote with the 'create function...' command, whose name if mem= ory serves correctly happens to begin by 'check...' because that happens to= be the most descriptive word for what it does. 

<= div>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 th= at 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'.

= I attach the text of the entire create function instruction, and also of th= e create table instruction. 

CREATE FUNCTION public.check_account_from_bill= _items(id integer) RETURNS boolean
    LANGUAGE plpgsql=
    AS $$
DECLARE
exp varchar;

BEGIN
select into exp account from bill_ite= ms where account in (select name from accounts where type=3D'Expense' or ty= pe=3D'Asset' or type=3D'Stock') and bill_items.id=3Did;
if exp is= not null then
return true;
else
return false= ;
end if;

END;
$$;
<= br>
-------------------------------------------

CREATE TABLE public.accounts (
    name cha= racter varying NOT NULL,
    type character varying NOT= NULL,
    vat real,
    account_na= me character varying,
    account_number character vary= ing,
    sorting_code character varying,
&nbs= p;   security_code character varying,
    website = character varying,
    email character varying,
    username character varying,
    passwo= rd character varying,
    institution character varying= ,
    address text,
    telephone c= haracter varying,
    description text,
 = ;   security_no_for_telephone character varying,
  &nbs= p; id integer NOT NULL,
    subtype character varying,<= /div>
    jnbp numeric(3,2) DEFAULT 0.5,
  &nb= sp; currency character(3) DEFAULT 'GBP'::bpchar,
    fo= reign_currency character(3),
    stopped_using date,
    vat_purchase boolean DEFAULT false,
  =   vat_income boolean DEFAULT false,
    balance_sh= eet_type character varying
);


=
ALTER TABLE public.accounts 


Looking at the documentation for Create Function, I'm not e= ntirely sure whether there is anything in there which is not supposed to be= there. At any rate, the system does not seem to allow the creation of func= tions that are 'illegal' for the most part, and I cannot see why anything i= n here WOULD be illegal.
 
We are working with ver= sion 10.23, and I cannot send you the entire output of the pg_dump file bec= ause it is 3.3 GB, but am happy to send you any parts that might help. = ;

Many thanks for you help - any further suggesti= ons would be great!

=20
=20
On Monday, 18 September 2023 at 20:29:19 BST, Laurenz A= lbe <laurenz.albe@cybertec.at> wrote:


On Mon, 2023-09-18 at 15:08 +0000, Ha= rry Green wrote:

> 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/p= gsql-language functions which include an sql statement referencing a table = are
> trying to be executed before the table which the= y 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...)&= nbsp;on line 95,
> but the instruction to create the a= ccounts table  '... create table public.accounts ... '
> appears on line 510.  
>
> Consequently, the restore does not work because the relation= s are created in the
> wrong order. Any ideas how I ca= n solve it?
>
> Please see the e= rror message below:
>
> psql:202= 30913_1300.sql: ERROR:  relation "accounts" does not exist
> LINE 1: select          name from acco= unts where (type=3D'Expense' or ...
>     &n= bsp;                     =         ^
> QUERY:  select&nb= sp;         name from accounts where (type=3D'Expense' = or type=3D'Asset' or type=3D'Stock') and name=3Daccount
&= gt; CONTEXT:  PL/pgSQL function public.check_account_from_bill_items(c= haracter varying) line 6 at SQL statement
> COPY bill_= items, line 1: "4096 Website 0 11.6099999999999994 \N \N 0 11.6099999999999= 994 5852 1 \N"
>
> psql:20230913= _1300.sql:201718: ERROR:  relation "ebooks" does not exist
> LINE 1: select count(*) from ebooks where isbn13=3D$1 
>                =              ^
> Q= UERY:  select count(*) from ebooks where isbn13=3D$1 
> CONTEXT:  SQL function "ebook_records_with_isbn13" during = inlining
> COPY book_information, line 1: "Title abcd = \N \N 1-2222-2222-1 111-1-2222-2222-1 11.11 111.11 1 \N ..."
>
> [...]
>
> psql:20230913_1300.sql:20459125: ERROR:  insert or update on= table "book_praise" violates foreign key constraint "book_praise_title_fke= y"
> DETAIL:  Key (title, binding, isbn)=3D(XXCCC= , back, 1111-55) is not present in table "book_information".
>
> psql:20230913_1300.sql:20459381: ERROR:&nb= sp; insert or update on table "posters" violates foreign key constraint "po= sters_isbn_fkey"
> DETAIL:  Key (isbn)=3D(1-3333-= 3333-3) is not present in table "book_information".

>
> [...]

It = seems like you are restoring the dump in the wrong way.
<= br clear=3D"none">pg_dump dumps foreign key constraints last of all, so dur= ing a restore they are created
after all the data have be= en inserted into the tables, and the error you see can never
happen.  You must be restoring a data-only dump into an already ex= isting schema with
foreign keys in place.  That won'= t work and isn't supported.  Don't forget that foreign
keys can be circular, and there might be no "correct order" to dump the = tables.

The first errors with the func= tion calls are less obvious, but existing triggers on the
tables micht be an explanation.  Since one of the functions is called= "check_...",
another explanation could be that you have = check constraints that use functions that
access other ta= bles.  That won't work and is not allowed.

Without knowing more, I cannot be certain what exactly is wrong, = but it doesn't look
like a PostgreSQL bug to me.  Pe= rhaps you can provide more details.

Yo= urs,
Laurenz Albe



------=_Part_6529175_2109166727.1695232793332--