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.a...@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
