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 1qiFs4-005kEy-9o for pgsql-general@arkaria.postgresql.org; Mon, 18 Sep 2023 15:09:09 +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 1qiFs2-001kZs-6r for pgsql-general@arkaria.postgresql.org; Mon, 18 Sep 2023 15:09:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qiFs1-001kZg-91 for pgsql-general@lists.postgresql.org; Mon, 18 Sep 2023 15:09:05 +0000 Received: from sonic306-20.consmr.mail.ir2.yahoo.com ([77.238.176.206]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qiFrv-005v8T-Tx for pgsql-general@lists.postgresql.org; Mon, 18 Sep 2023 15:09:04 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1695049736; bh=hOfjdLryF0po4vjmqNfTVVi0CwrQoriDOD2OU2EV/G4=; h=Date:From:To:Subject:References:From:Subject:Reply-To; b=aHK2+3xlO963/EGIgHPlNum5589NrJfIuQrjOvxRrULlOyKTYSmowZUlLFzv9B6zdSdvdHQXtXV7cRbypr+s0QZ+u1onWfCXE0q50pAccRQQAdVePCRRuMKOkdIWJ1PgCqpO4Q5si5IvHr2pPxtoolhiPk2VVmcQAKFRjKYB0gEcIry/QGIU1Wjs52RdflpS8+tVDUHu9T6sTEVOTwZIoyoa3noWJRyfLzBJzRB4+I0L5phMmBYLKD9jabwiojFdgh5Kz1kf9nxq2WFpLtzdo52wjY2HJn/ha4zqlk477etpJ7sGbW+gq8aobjsvM/2PvqoTP/JUq/hixcFyKe3/2w== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1695049736; bh=Xweu3zClJhtE4sCNgSp4ZLp5unkifC38GFeXxdqmD47=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=sAtghEz8E1yiFoPBRXbUWpRpo60YYadSn84kWEEVNHtBKlrZC4Ro/5Xr14AP069dxbD/yxiA6kS8+HxFwpoSLXKhvhImdUGC7a43PBrcwBSA4XOuLjQLhr10C+I2xm+XlpZp285GC51/SwM7S4EKLnpOHEUqLSRyR0gmgnYVKT5uJXsP/IjFEWxWuVqWR9cEpEwqBP5SC/j8Gyd3Uz4cRS62J+517WOkEUgKlyOKLqnIlx7JWHEW3Ry3ZkLeAvKrQo/3PcIlJJGZ4KwbM9t+lziEncu+qOyxh5IEVptJDkaaXM6eCwIZUA9qijNOyP6I/08eN8wXYpCz2FuJQ4+RBQ== X-YMail-OSG: 7pfnFkIVM1m34Yf6iF1YIigTO1GxI5Gmcw4kwLv3XqfFQgHDp6qjYxC9AcVKjnJ SxNYmNVgm3hDaiBfq.0nLAcVDw0UCOuLJbfduz1lxLhvDq9N3ci_G.Isn5jbngU1nEK_6fTk4Ycs 9mr1CGtXkcWMasg1KKioToeZoI4xUi_0u5NsdgUz.cKjVoLWT_tGtFKmGwqSEkCrOORwk9r_qGvB al_JGrhjxiOBSMEuG2Kj_BbubEq0laEgovxwy3P9VEdnXG_54WOAuFT5Yckss9ZNhMMUgeoL311T NbvnUmUnOhaM3JG_waArbBgtNes9ARLudbqFTaLo8SXjPfGoK2YAzf2HFcQucdIUyOfR1nlpHGPy xzAqVeWJH.p5Fh00iOvebiYEgx_BbNrRiDszIyaM8EfM49t_G_9N4uDvfunshW485ocn6Nspzie5 oIUGAIuGiOo7v4I1eIrVFBkyoLnHtliNO2zGs4Mu5fuSshGPzfZp5uTzRy2rK.T9dKsjM5BQ1XXM nCdueitAuwRWcH5eVPh7c_fs3qoIpKZx6krx6oLp72e1tpNygEPUd1WjSniP5cgQFPwpH7nNKHMj RmUnW7X86Z5eZBSLi1.K4yUgKhrsDTDaRHHXsWtUHjNIRBI8mkH6kPPBJaxx4n2Bqid6t_MsLiXS t1Ilshg_PyDkizx0JiEsHLr_FuxI_Jj5U7_omMJZiGJ8RDsfDyXmNGYPZRn812wq60p6LV4043L9 exqjzCOe8iptoDHVyY4wHCgPsrWxjbYzBxd1o0OQPWl0JhhHrJHWKozw9RHFpPt_cYimlyfcgdq8 MHYYFgaQ_IV_ZLXVADpjecR.uqgiaJOMvNzw_Q4_Blk58PUS9Pus9Xex9CcdkEEwp0jClZuLEzOX nN9vyEqP6p8z61xvTXaz.ZkDgmdZJKeFg50EVDunmyZx2DX3B1PGYLl1xYqrTDWY.hR.ocAkog.h 2dsgENGPJsOZNDFszCZDwiP7XxPm4ZaX_RygWxH3iLtuRxtaLJBGDr3jbPCvoNLVnWdjgiskDpka k34_T0RHnygjs6OBTUWnH7fTsgg2CPGvvAJdjsl_RnHRohwJ3X384fxihtXbByTk8gwYufHd8IzV hHUUVJ9u2mDWv9U_OfkCIqXeg2m2Mhn.4I3dA3Yt2TWgzQhtMJrQTT1Lm9Dk2FSCoQd0aStsT8hS oAq5teJXZDQWkkg9cwkMJTOJhIBPFxG2fMAXjJGEP1UPM2C4L1MuMbQrXmPrbh8FADt4tm2kRvtE J2yvlwDhIBuwh2G564VhjJAiR7hYsgOmQ88TjhWmArv0GYCGEUuwmUUSjQtHLmTLw65jzgAASYbe iTShcoE_wIqUc07ge27VY0218MG8I7qwMmdCDFRBxhP63wkJbk4nDoQDbpdYjDLr2ThN27XSMOzX 2h8845yyOlC7KxFhqJEE.JV4c5U9WYg5wWD_cyNIVymsh50_A2tmI8tlxo4r1qI.UA_q1iClv84m MkRBHbCggApt0Zk9xBwCilPAX0E1l.xhYOblYBmWLyMGC3xQcWl.K4cdz4Povh17_xA_1Uz4RwhS qsaMLcmiUwcRI0S_2nyekW7Z.h5BqvIdcUT05v5E32fmVNYSxKArbCs7n.1gc.teDCeUJQDSAdKl 1exaJNJ_JZd49OiJm8q4snXPK7lGhcglVMmqxZQJHdmDo.1qncxQR2UfYXmKMvQCegSa2pUvyT1T QftrATVrJW6td.CG13L2F8UaUmyG819GUgFEVCLgyIHHHSeRvoX_KB.SI2Djif.YOo.NR3BY7DyJ 442aVHse4vZhSJQcKv_AwpwNlL_zokeUzBF..L3YydHKLZqTczPfvxxO5NVOQ2wpmZTNn7doIFU1 s5bG.OvHkffpQnhMswOjryzdIIoTbVeOT3jiTZ5QFGvCkOF04ZjU7rq7q3N8bCT0dpzt7zSHW.g4 cWTHJyxSpdmb5j8FyLjqu4USsCqRHEnCa1psm_6ACuHnTwxiAv.gg0FUHyND_3sW8KWr_UD0GijE UDkcJIXWKaSm3y8RSWqX2o4zih7Kn5D42L7Ag_1H0sy5Mgp8AcKU9.ajCxx0HiA4.QUVlva8CKBL YX0QF.FlEQuXF3rqrdhla_Wdw5FvVTnScpayl3t6sy7xgME2B7zQuu4AZ1RlCxVCpPdwTos0A69y FSCXJrgn96TiS_hl5yNKfd6WQ.Q-- X-Sonic-MF: X-Sonic-ID: 6b0e9be1-cbed-4b4d-8ec8-7be38ecf2590 Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.ir2.yahoo.com with HTTP; Mon, 18 Sep 2023 15:08:56 +0000 Date: Mon, 18 Sep 2023 15:08:49 +0000 (UTC) From: Harry Green To: "pgsql-general@lists.postgresql.org" Message-ID: <73791286.4632684.1695049729344@mail.yahoo.com> Subject: 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_4632683_355448592.1695049729341" References: <73791286.4632684.1695049729344.ref@mail.yahoo.com> X-Mailer: WebService/1.1.21797 YMailNorrin Content-Length: 15794 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_4632683_355448592.1695049729341 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 ref= erencing a table are trying to be executed before the table which they refe= rence has been created. I am surprised that pg_dump could get the order wro= ng, but that is what is happening.=C2=A0 Looking at the first of the error messages, the pl/pgsql function is create= d as=C2=A0 create=C2=A0 function public.check_account_from_bill_items(chara= cter...)=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 Consequently, the restore does not work because the relations are created i= n the wrong order. Any ideas how I can solve it? Please see the error message below: psql:20230913_1300.sql: ERROR:=C2=A0 relation "accounts" does not existLINE= 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=3DaccountCONTEXT= :=C2=A0 PL/pgSQL function public.check_account_from_bill_items(character va= rying) line 6 at SQL statementCOPY bill_items, line 1: "4096 Website 0 11.6= 099999999999994 \N \N 0 11.6099999999999994 5852 1 \N" psql:20230913_1300.sql:201718: ERROR:=C2=A0 relation "ebooks" does not exis= tLINE 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=A0CONTEXT:=C2=A0 SQL function "ebook_records_with_isbn13" during inlin= ingCOPY book_information, line 1: "Title abcd \N \N 1-2222-2222-1 111-1-222= 2-2222-1 11.11 111.11 1 \N ..." psql:20230913_1300.sql:1181710: ERROR:=C2=A0 relation "book_information" do= es not existLINE 1: select e_isbn from book_information where title=3D$1 li= mit 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^QUERY:=C2=A0 select e_isbn from book_informatio= n where title=3D$1 limit 1CONTEXT:=C2=A0 SQL function "e_isbn_of" during in= liningCOPY ebooks, line 1: "XYZ \N ..." psql:20230913_1300.sql:2670734: ERROR:=C2=A0 relation "book_information" do= es not existLINE 1: select=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 id from book_i= nformation where (title,bindin...=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 ^QUER= Y:=C2=A0 select=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 id from book_information = where (title,binding,LOWER(isbn))=3D(title1,binding1,LOWER(isbn1))CONTEXT:= =C2=A0 PL/pgSQL function public.in_book_information(character varying,chara= cter varying,character varying) line 6 at SQL statementCOPY invoice_details= , line 1: "vvvvvv Cr 200800432 29243 ..." psql:20230913_1300.sql:20459005: ERROR:=C2=A0 insert or update on table "bi= _authors" violates foreign key constraint "bi_authors_book_information_id_f= key"DETAIL:=C2=A0 Key (book_information_id)=3D(10442) is not present in tab= le "book_information". psql:20230913_1300.sql:20459125: ERROR:=C2=A0 insert or update on table "bo= ok_praise" violates foreign key constraint "book_praise_title_fkey"DETAIL:= =C2=A0 Key (title, binding, isbn)=3D(XXCCC, back, 1111-55) is not present i= n table "book_information". psql:20230913_1300.sql:20459381: ERROR:=C2=A0 insert or update on table "po= sters" violates foreign key constraint "posters_isbn_fkey"DETAIL:=C2=A0 Key= (isbn)=3D(1-3333-3333-3) is not present in table "book_information". psql:20230913_1300.sql:20459413: ERROR:=C2=A0 insert or update on table "pr= eorder_items" violates foreign key constraint "preorder_items_title_fkey"DE= TAIL:=C2=A0 Key (title, binding, isbn)=3D(xxxx, xxxx, x) is not present in = table "book_information". psql:20230913_1300.sql:20459461: ERROR:=C2=A0 insert or update on table "pu= rchase_orders" violates foreign key constraint "purchase_orders_isbn_fkey"D= ETAIL:=C2=A0 Key (isbn)=3D(2-22222-12-1) is not present in table "book_info= rmation". psql:20230913_1300.sql:20459469: ERROR:=C2=A0 insert or update on table "pu= rchase_orders" violates foreign key constraint "purchase_orders_item_fkey"D= ETAIL:=C2=A0 Key (item, type_or_binding, isbn)=3D(title, back, 2222) is not= present in table "book_information". psql:20230913_1300.sql:20459653: ERROR:=C2=A0 insert or update on table "re= curring_orders_detail" violates foreign key constraint "recurring_orders_de= tail_isbn_fkey"DETAIL:=C2=A0 Key (isbn)=3D(0-1111-1) is not present in tabl= e "book_information". psql:20230913_1300.sql:20459701: ERROR:=C2=A0 insert or update on table "ro= yalties_paid" violates foreign key constraint "royalties_paid_bill_item_id_= fkey"DETAIL:=C2=A0 Key (bill_item_id)=3D(3151) is not present in table "bil= l_items". psql:20230913_1300.sql:20459765: ERROR:=C2=A0 insert or update on table "sp= ine_locations" violates foreign key constraint "spine_locations_isbn_fkey"D= ETAIL:=C2=A0 Key (isbn)=3D(0-00000) is not present in table "book_informati= on". psql:20230913_1300.sql:20459773: ERROR:=C2=A0 insert or update on table "st= ock_boxed" violates foreign key constraint "stock_boxed_isbn_fkey"DETAIL:= =C2=A0 Key (isbn)=3D(1-2121) is not present in table "book_information". psql:20230913_1300.sql:20459781: ERROR:=C2=A0 insert or update on table "st= ock_boxed" violates foreign key constraint "stock_boxed_title_fkey"DETAIL:= =C2=A0 Key (title, binding, isbn)=3D(title, back, 3-3333) is not present in= table "book_information". psql:20230913_1300.sql:20459805: ERROR:=C2=A0 insert or update on table "st= ock_corrections" violates foreign key constraint "stock_corrections_title_f= key"DETAIL:=C2=A0 Key (title, binding)=3D(Title asd, back) is not present i= n table "book_information". psql:20230913_1300.sql:20459837: ERROR:=C2=A0 insert or update on table "st= ock_transfers" violates foreign key constraint "stock_transfers_title_fkey"= DETAIL:=C2=A0 Key (title, binding, isbn)=3D(The, Hardback, 0-0000) is not p= resent in table "book_information". Thanks a lot! ------=_Part_4632683_355448592.1695049729341 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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 refe= rencing a table are trying to be executed before the table which they refer= ence has been created. I am surprised that pg_dump could get the order wron= g, but that is what is happening. 
Looking at the first of the err= or messages, the pl/pgsql function is created as  create  f= unction 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.  =

Consequently, the restore does not work because the relations are crea= ted in the wrong order. Any ideas how I can solve it?

Please see the error message below:

psql:20230913_1300.sql: ERROR:  relation "accounts" does not e= xist
LINE 1: select  &nbs= p;       name from accounts where (type=3D'Expense' or ...
        &nb= sp;                     &= nbsp;   ^
QUERY:  se= lect          name from accounts where (type=3D'Ex= pense' or type=3D'Asset' or type=3D'Stock') and name=3Daccount
=
CONTEXT:  PL/pgSQL function public.ch= eck_account_from_bill_items(character varying) line 6 at SQL statement
COPY bill_items, line 1: "4096=09Website=090=0911.6099999999999994=09\N=09\N=090=0911.6099999= 999999994=095852=091=09\N"

psql:20230913_1300.s= ql:201718: ERROR:  relation "ebooks" does not exist
LINE 1: = select count(*) from ebooks where isbn13=3D$1 
   =                     &nbs= p;    ^
QUERY:  select count(*) from ebooks where = isbn13=3D$1 
CONTEXT:  SQL function "ebook_records_with= _isbn13" during inlining
COPY book_information, line 1: "Title ab= cd=09\N=09\N=091-2222-2222-1= =09111-1-2222-2222-1=0911.11=09111.11=091=09\N=09..."

psql:20230913_1300.sql:1181710: ERROR:  relation "book_information" d= oes not exist
LINE 1: select e_isbn from book_information where t= itle=3D$1 limit 1
             = ;              ^
QUERY:  = select e_isbn from book_information where title=3D$1 limit 1
CONT= EXT:  SQL function "e_isbn_of" during inlining
COPY ebooks, = line 1: "XYZ=09\N=09..."

psql:20230913_1300.sql:2670734: ERROR:  relation "book_information" d= oes not exist
LINE 1: select          id= from book_information where (title,bindin...
     = ;                     &nb= sp;     ^
QUERY:  select       = ;   id from book_information where (title,binding,LOWER(isbn))=3D(titl= e1,binding1,LOWER(isbn1))
CONTEXT:  PL/pgSQL function public= .in_book_information(character varying,character varying,character varying)= line 6 at SQL statement
COPY invoice_details, line 1: "vvvvvv=09Cr 200800432 29243 ..."

psql:20230913= _1300.sql:20459005: ERROR:  insert or update on table "bi_authors" vio= lates foreign key constraint "bi_authors_book_information_id_fkey"
DETAIL:  Key (book_information_id)=3D(10442) is not present in table= "book_information".

psql:20230913_1300.sql:204591= 25: ERROR:  insert or update on table "book_praise" violates foreign k= ey constraint "book_praise_title_fkey"
DETAIL:  Key (title, = binding, isbn)=3D(XXCCC, back, 1111-55) is not present in table "book_infor= mation".

psql:20230913_1300.sql:20459381: ERROR:&n= bsp; insert or update on table "posters" violates foreign key constraint "p= osters_isbn_fkey"
DETAIL:  Key (isbn)=3D(1-3333-3333-3) is n= ot present in table "book_information".

psql:20230= 913_1300.sql:20459413: ERROR:  insert or update on table "preorder_ite= ms" violates foreign key constraint "preorder_items_title_fkey"
D= ETAIL:  Key (title, binding, isbn)=3D(xxxx, xxxx, x) is not present in= table "book_information".

psql:20230913_1300.sql:= 20459461: ERROR:  insert or update on table "purchase_orders" violates= foreign key constraint "purchase_orders_isbn_fkey"
DETAIL: = Key (isbn)=3D(2-22222-12-1) is not present in table "book_information".

psql:20230913_1300.sql:20459469: ERROR:  insert= or update on table "purchase_orders" violates foreign key constraint "purc= hase_orders_item_fkey"
DETAIL:  Key (item, type_or_binding, = isbn)=3D(title, back, 2222) is not present in table "book_information".

psql:20230913_1300.sql:20459653: ERROR:  insert = or update on table "recurring_orders_detail" violates foreign key constrain= t "recurring_orders_detail_isbn_fkey"
DETAIL:  Key (isbn)=3D= (0-1111-1) is not present in table "book_information".

=
psql:20230913_1300.sql:20459701: ERROR:  insert or update on tabl= e "royalties_paid" violates foreign key constraint "royalties_paid_bill_ite= m_id_fkey"
DETAIL:  Key (bill_item_id)=3D(3151) is not prese= nt in table "bill_items".

psql:20230913_1300.sql:2= 0459765: ERROR:  insert or update on table "spine_locations" violates = foreign key constraint "spine_locations_isbn_fkey"
DETAIL:  = Key (isbn)=3D(0-00000) is not present in table "book_information".

psql:20230913_1300.sql:20459773: ERROR:  insert or up= date on table "stock_boxed" violates foreign key constraint "stock_boxed_is= bn_fkey"
DETAIL:  Key (isbn)=3D(1-2121) is not present in ta= ble "book_information".

psql:20230913_1300.sql:204= 59781: ERROR:  insert or update on table "stock_boxed" violates foreig= n key constraint "stock_boxed_title_fkey"
DETAIL:  Key (titl= e, binding, isbn)=3D(title, back, 3-3333) is not present in table "book_inf= ormation".

psql:20230913_1300.sql:20459805: ERROR:=   insert or update on table "stock_corrections" violates foreign key c= onstraint "stock_corrections_title_fkey"
DETAIL:  Key (title= , binding)=3D(Title asd, back) is not present in table "book_information".<= /div>

psql:20230913_1300.sql:20459837: ERROR:  inse= rt or update on table "stock_transfers" violates foreign key constraint "st= ock_transfers_title_fkey"
DETAIL:  Key (title, binding, isbn= )=3D(The, Hardback, 0-0000) is not present in table "book_information".



Thanks a lot!
<= div dir=3D"ltr" data-setdir=3D"false" style=3D"font-size: 16px;">
=

------=_Part_4632683_355448592.1695049729341--