pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
8+ messages / 4 participants
[nested] [flat]

* trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
@ 2023-09-18 15:08 Harry Green <harrygreen91@yahoo.com>
  2023-09-18 15:16 ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Adrian Klaver <adrian.klaver@aklaver.com>
  2023-09-18 15:40 ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order David G. Johnston <david.g.johnston@gmail.com>
  2023-09-18 19:28 ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 3 replies; 8+ messages in thread

From: Harry Green @ 2023-09-18 15:08 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.  
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:
psql:20230913_1300.sql: ERROR:  relation "accounts" does not existLINE 1: select          name from accounts where (type='Expense' or ...                                  ^QUERY:  select          name from accounts where (type='Expense' or type='Asset' or type='Stock') and name=accountCONTEXT:  PL/pgSQL function public.check_account_from_bill_items(character varying) line 6 at SQL statementCOPY bill_items, line 1: "4096 Website 0 11.6099999999999994 \N \N 0 11.6099999999999994 5852 1 \N"
psql:20230913_1300.sql:201718: ERROR:  relation "ebooks" does not existLINE 1: select count(*) from ebooks where isbn13=$1                              ^QUERY:  select count(*) from ebooks where isbn13=$1 CONTEXT:  SQL function "ebook_records_with_isbn13" during inliningCOPY 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:1181710: ERROR:  relation "book_information" does not existLINE 1: select e_isbn from book_information where title=$1 limit 1                           ^QUERY:  select e_isbn from book_information where title=$1 limit 1CONTEXT:  SQL function "e_isbn_of" during inliningCOPY ebooks, line 1: "XYZ \N ..."
psql:20230913_1300.sql:2670734: ERROR:  relation "book_information" does not existLINE 1: select          id from book_information where (title,bindin...                                ^QUERY:  select          id from book_information where (title,binding,LOWER(isbn))=(title1,binding1,LOWER(isbn1))CONTEXT:  PL/pgSQL function public.in_book_information(character varying,character varying,character varying) line 6 at SQL statementCOPY invoice_details, line 1: "vvvvvv Cr 200800432 29243 ..."
psql:20230913_1300.sql:20459005: ERROR:  insert or update on table "bi_authors" violates foreign key constraint "bi_authors_book_information_id_fkey"DETAIL:  Key (book_information_id)=(10442) is not present in table "book_information".
psql:20230913_1300.sql:20459125: ERROR:  insert or update on table "book_praise" violates foreign key constraint "book_praise_title_fkey"DETAIL:  Key (title, binding, isbn)=(XXCCC, back, 1111-55) is not present in table "book_information".
psql:20230913_1300.sql:20459381: ERROR:  insert or update on table "posters" violates foreign key constraint "posters_isbn_fkey"DETAIL:  Key (isbn)=(1-3333-3333-3) is not present in table "book_information".
psql:20230913_1300.sql:20459413: ERROR:  insert or update on table "preorder_items" violates foreign key constraint "preorder_items_title_fkey"DETAIL:  Key (title, binding, isbn)=(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)=(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 "purchase_orders_item_fkey"DETAIL:  Key (item, type_or_binding, isbn)=(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 constraint "recurring_orders_detail_isbn_fkey"DETAIL:  Key (isbn)=(0-1111-1) is not present in table "book_information".
psql:20230913_1300.sql:20459701: ERROR:  insert or update on table "royalties_paid" violates foreign key constraint "royalties_paid_bill_item_id_fkey"DETAIL:  Key (bill_item_id)=(3151) is not present in table "bill_items".
psql:20230913_1300.sql:20459765: ERROR:  insert or update on table "spine_locations" violates foreign key constraint "spine_locations_isbn_fkey"DETAIL:  Key (isbn)=(0-00000) is not present in table "book_information".
psql:20230913_1300.sql:20459773: ERROR:  insert or update on table "stock_boxed" violates foreign key constraint "stock_boxed_isbn_fkey"DETAIL:  Key (isbn)=(1-2121) is not present in table "book_information".
psql:20230913_1300.sql:20459781: ERROR:  insert or update on table "stock_boxed" violates foreign key constraint "stock_boxed_title_fkey"DETAIL:  Key (title, binding, isbn)=(title, back, 3-3333) is not present in table "book_information".
psql:20230913_1300.sql:20459805: ERROR:  insert or update on table "stock_corrections" violates foreign key constraint "stock_corrections_title_fkey"DETAIL:  Key (title, binding)=(Title asd, back) is not present in table "book_information".
psql:20230913_1300.sql:20459837: ERROR:  insert or update on table "stock_transfers" violates foreign key constraint "stock_transfers_title_fkey"DETAIL:  Key (title, binding, isbn)=(The, Hardback, 0-0000) is not present in table "book_information".


Thanks a lot!

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
  2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
@ 2023-09-18 15:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-09-18 15:20   ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Adrian Klaver <adrian.klaver@aklaver.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2023-09-18 15:16 UTC (permalink / raw)
  To: Harry Green <harrygreen91@yahoo.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
  2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
  2023-09-18 15:16 ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-09-18 15:20   ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2023-09-18 15:20 UTC (permalink / raw)
  To: Harry Green <harrygreen91@yahoo.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 9/18/23 08:16, Adrian Klaver wrote:
> 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.

Forgot to add to above, that if you want to do this sort of thing then 
use a trigger. In a dump/restore they are added back to the tables after 
the tables and table data have been restored.

> 
>>
>> 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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
  2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
@ 2023-09-18 15:40 ` David G. Johnston <david.g.johnston@gmail.com>
  2 siblings, 0 replies; 8+ messages in thread

From: David G. Johnston @ 2023-09-18 15:40 UTC (permalink / raw)
  To: Harry Green <harrygreen91@yahoo.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Mon, Sep 18, 2023 at 8:32 AM Harry Green <harrygreen91@yahoo.com> wrote:

> Any ideas how I can solve it?
>

It is most likely you have broken some kind of rule in your database
schema, but without seeing the dump file it is impossible to point out what
you have done wrong.

Relatedly, the bodies of functions are black-boxes, it is not possible to
establish dependencies between them.  This limitation is why many of the
rules I allude to above exist.

You have also not mentioned what version you are working with.

David J.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
  2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
@ 2023-09-18 19:28 ` Laurenz Albe <laurenz.albe@cybertec.at>
  2023-09-20 17:59   ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Laurenz Albe @ 2023-09-18 19:28 UTC (permalink / raw)
  To: Harry Green <harrygreen91@yahoo.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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_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.  
> 
> 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:
> 
> psql:20230913_1300.sql: ERROR:  relation "accounts" does not exist
> LINE 1: select          name from accounts where (type='Expense' or ...
>                                   ^
> QUERY:  select          name from accounts where (type='Expense' or type='Asset' or type='Stock') and name=account
> CONTEXT:  PL/pgSQL function public.check_account_from_bill_items(character varying) line 6 at SQL statement
> COPY bill_items, line 1: "4096 Website 0 11.6099999999999994 \N \N 0 11.6099999999999994 5852 1 \N"
> 
> psql:20230913_1300.sql:201718: ERROR:  relation "ebooks" does not exist
> LINE 1: select count(*) from ebooks where isbn13=$1 
>                              ^
> QUERY:  select count(*) from ebooks where isbn13=$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_fkey"
> DETAIL:  Key (title, binding, isbn)=(XXCCC, back, 1111-55) is not present in table "book_information".
> 
> psql:20230913_1300.sql:20459381: ERROR:  insert or update on table "posters" violates foreign key constraint "posters_isbn_fkey"
> DETAIL:  Key (isbn)=(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 see can never
happen.  You must be restoring a data-only dump into an already existing 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 function 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 tables.  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.  Perhaps you can provide more details.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
  2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
  2023-09-18 19:28 ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-09-20 17:59   ` Harry Green <harrygreen91@yahoo.com>
  2023-09-20 18:16     ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Adrian Klaver <adrian.klaver@aklaver.com>
  2023-09-20 20:42     ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 2 replies; 8+ messages in thread

From: Harry Green @ 2023-09-20 17:59 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>

 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'.
I attach the text of the entire create function instruction, and also of the create table instruction. 
 CREATE FUNCTION public.check_account_from_bill_items(id integer) RETURNS boolean    LANGUAGE plpgsql    AS $$DECLAREexp varchar;
BEGINselect into exp account from bill_items where account in (select name from accounts where type='Expense' or type='Asset' or type='Stock') and bill_items.id=id;if exp is not null thenreturn true;elsereturn false;end if;
END;$$;
-------------------------------------------
CREATE TABLE public.accounts (    name character varying NOT NULL,    type character varying NOT NULL,    vat real,    account_name character varying,    account_number character varying,    sorting_code character varying,    security_code character varying,    website character varying,    email character varying,    username character varying,    password character varying,    institution character varying,    address text,    telephone character varying,    description text,    security_no_for_telephone character varying,    id integer NOT NULL,    subtype character varying,    jnbp numeric(3,2) DEFAULT 0.5,    currency character(3) DEFAULT 'GBP'::bpchar,    foreign_currency character(3),    stopped_using date,    vat_purchase boolean DEFAULT false,    vat_income boolean DEFAULT false,    balance_sheet_type character varying);

ALTER TABLE public.accounts 

Looking at the documentation for Create Function, I'm not entirely 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 functions that are 'illegal' for the most part, and I cannot see why anything in here WOULD be illegal. We 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 send you any parts that might help. 
Many thanks for you help - any further suggestions would be great!
    On Monday, 18 September 2023 at 20:29:19 BST, Laurenz Albe <laurenz.albe@cybertec.at> wrote:  
 
 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_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.  
> 
> 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:
> 
> psql:20230913_1300.sql: ERROR:  relation "accounts" does not exist
> LINE 1: select          name from accounts where (type='Expense' or ...
>                                   ^
> QUERY:  select          name from accounts where (type='Expense' or type='Asset' or type='Stock') and name=account
> CONTEXT:  PL/pgSQL function public.check_account_from_bill_items(character varying) line 6 at SQL statement
> COPY bill_items, line 1: "4096 Website 0 11.6099999999999994 \N \N 0 11.6099999999999994 5852 1 \N"
> 
> psql:20230913_1300.sql:201718: ERROR:  relation "ebooks" does not exist
> LINE 1: select count(*) from ebooks where isbn13=$1 
>                              ^
> QUERY:  select count(*) from ebooks where isbn13=$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_fkey"
> DETAIL:  Key (title, binding, isbn)=(XXCCC, back, 1111-55) is not present in table "book_information".
> 
> psql:20230913_1300.sql:20459381: ERROR:  insert or update on table "posters" violates foreign key constraint "posters_isbn_fkey"
> DETAIL:  Key (isbn)=(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 see can never
happen.  You must be restoring a data-only dump into an already existing 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 function 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 tables.  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.  Perhaps you can provide more details.

Yours,
Laurenz Albe


  

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
  2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
  2023-09-18 19:28 ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Laurenz Albe <laurenz.albe@cybertec.at>
  2023-09-20 17:59   ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
@ 2023-09-20 18:16     ` Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2023-09-20 18:16 UTC (permalink / raw)
  To: Harry Green <harrygreen91@yahoo.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>


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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
  2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
  2023-09-18 19:28 ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Laurenz Albe <laurenz.albe@cybertec.at>
  2023-09-20 17:59   ` Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
@ 2023-09-20 20:42     ` Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 0 replies; 8+ messages in thread

From: Laurenz Albe @ 2023-09-20 20:42 UTC (permalink / raw)
  To: Harry Green <harrygreen91@yahoo.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Wed, 2023-09-20 at 17:59 +0000, Harry Green wrote:
> 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'.

Ok, so my conjecture about check constraints was wrong.

The fact remains that you have to restore the dump into an empty database.
Then these errors should not occur.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-09-20 20:42 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-18 15:08 trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Harry Green <harrygreen91@yahoo.com>
2023-09-18 15:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-09-18 15:20   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-09-18 15:40 ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-18 19:28 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-20 17:59   ` Harry Green <harrygreen91@yahoo.com>
2023-09-20 18:16     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-09-20 20:42     ` Laurenz Albe <laurenz.albe@cybertec.at>

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