PostgreSQL v12.4 pg_dumpall output fails to load via "psql -f" load to empty instance/server.
This occurs using check constraints that reference a function using data in a table that is loaded later.
As a logical backup method this should be loadable into a clean database as-is.
Error:
psql:TestCase1.sql:286: ERROR: The schema/table/column cannot be found in a Domain Relation
CONTEXT: PL/pgSQL function frp_pkg_picklist.is_item_valid_for_column(character varying,character varying,character varying,character varying,boolean,boolean) line 19 at RAISE
COPY customer, line 1: "0 SHARED_DO_NOT_USE customer"
This can be remedied by manually modifying the pg_dumpall .sql file as a workaround
- Move the tables referenced in the check constraint function, and place at the beginning of the COPY section
- Disable the (or all) constraints on the affected tables and enable at the end.
- Do not create the (or all) constraints on the affected tables and create them at the end.
All of these require changes to the output file (risk and work), but it should load cleanly without that.
==========
Test Case
----------
I've minimized the situation to a small test-set to show how it works (or doesn't).
See TestCase1.txt
Also see 202110load.txt for initial discovery information.
- Create a fresh PostgreSQL database
- Load instance with TestCase1BuildDB.sql
e.g. psql -f TestCase1BuildDB.sql
- pg_dumpall instance
e.g. pg_dumpall -h0.0.0.0 -p5432 -Upostgres > TestCase1.sql
- Create a fresh PostgreSQL database
- Load databae from TestCase1.sql dumpall
e.g. psql -f TestCase1.sql
should fail with:
... ERROR: The schema/table/column cannot be found in a Domain Relation
... CONTEXT: PL/pgSQL function frp_pkg_picklist.is_item_valid_for_column(character varying,character varying,character varying,character varying,boolean,boolean) line 19 at RAISE
... COPY customer, line 1: "0 SHARED_DO_NOT_USE customer"
... COPY frp.customer (id, formal_name, accounting_period_scope) FROM stdin;
The check constraint on the table calls a function
and that functions makes a decision based on data in the picklist table
which has not been loaded yet, thus it fails the constraint check and does not load rows.