From: JED WALKER <jedwa@comcast.net>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: Jed@Home <jedwa@comcast.net>
Cc: Jed@ICD <jed.walker@icd-tech.com>
Subject: v12.4 pg_dump .sql fails to load data via psql
Date: Mon, 11 Oct 2021 16:26:17 -0600 (MDT)
Message-ID: <375102856.69594.1633991178400@connect.xfinity.com> (raw)
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
1. Move the tables referenced in the check constraint function, and place at the beginning of the COPY section
2. Disable the (or all) constraints on the affected tables and enable at the end.
3. 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.
1. Create a fresh PostgreSQL database
1. Load instance with TestCase1BuildDB.sql
e.g. psql -f TestCase1BuildDB.sql
1. pg_dumpall instance
e.g. pg_dumpall -h0.0.0.0 -p5432 -Upostgres > TestCase1.sql
1. Create a fresh PostgreSQL database
1. 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.
* Jed
Attachments:
TestCase1.sql (application/octet-stream, 10.0K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: jedwa@comcast.net, pgsql-bugs@lists.postgresql.org, jed.walker@icd-tech.com
Subject: Re: v12.4 pg_dump .sql fails to load data via psql
In-Reply-To: <375102856.69594.1633991178400@connect.xfinity.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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