Thread: BUG #15316: pg_restore failes to load data that is constrainted by afunctional check constraints

The following bug has been logged on the website:

Bug reference:      15316
Logged by:          Lloyd Albin
Email address:      lalbin@scharp.org
PostgreSQL version: 10.4
Operating system:   OpenSUSE
Description:

I have found an edge case where pg_dump and pg_restore fails to restore
databases with check constraints that points to data in another table that
is restored after the table containing the check constraint. While our
original test case used two schema's, you can also create this same issue
using a single schema. This has been tested and found to be a problem on
PostgreSQL 9.5.2, 10.3 and 10.4. On Windows, Enterprise SUSE and OpenSUSE.

The single schema error message:
ERROR:  new row for relation "table_a" violates check constraint
"table_a_chk_billing_acct_type"

The two schema error message:
COPY failed for table "table_a": ERROR:  relation "table_b" does not exist

The real reason for both error messages is the fact that the check
constraints get applied when the table is created in the pre-data portion of
the restore process. The check constraint needs to be moved to the post-data
portion of the restore process, so that the data will be there at the time
the check constraint is applied.

# Create our source database
createdb test_db_1
 
# Start psql to create the data within the database
psql -d test_db_1

-- Create the schema for the lookup table
CREATE SCHEMA schema_b;
 
-- Create the lookup table
CREATE TABLE schema_b.table_b (
  type VARCHAR NOT NULL,
  "desc" VARCHAR NOT NULL,
  CONSTRAINT table_b_pk PRIMARY KEY(type, "desc")
);
 
-- Create the schema for our main table
CREATE SCHEMA schema_a;
 
-- Create the check constraint function
CREATE OR REPLACE FUNCTION schema_b.check_lu_table_b (varchar, varchar)
RETURNS boolean AS'
SELECT CASE WHEN b."desc" IS NULL THEN FALSE ELSE TRUE END 
FROM (SELECT $2 AS "desc", $1 AS type) a LEFT JOIN
schema_b.table_b b USING (type, "desc")
'LANGUAGE 'sql'
VOLATILE
RETURNS NULL ON NULL INPUT;
 
-- Create the main table, using the check constraint function
CREATE TABLE schema_a.table_a (
  tp_id SERIAL,
  billing_acct_type VARCHAR(20),
  CONSTRAINT "table_a_pk" PRIMARY KEY(tp_id),
  CONSTRAINT "table_a_chk_billing_acct_type" CHECK
(schema_b.check_lu_table_b('BillAcctType', billing_acct_type))
);
 
-- Insert the test data
INSERT INTO schema_b.table_b VALUES ('BillAcctType', 'Invoice');
INSERT INTO schema_a.table_a (billing_acct_type) VALUES ('Invoice'); 

#Backup the test_db_1 database to test the restoration
pg_dump -Fc test_db_1 -f test_db_1.pg_dump

# Create the Database into
createdb test_db_2
 
# Failed Load
pg_restore -d test_db_2 test_db_1.pg_dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2770; 0 20015425 TABLE DATA
table_a postgres
pg_restore: [archiver (db)] COPY failed for table "table_a": ERROR:  new row
for relation "table_a" violates check constraint
"table_a_chk_billing_acct_type"
DETAIL:  Failing row contains (1, Invoice).
CONTEXT:  COPY table_a, line 1: "1      Invoice"
WARNING: errors ignored on restore: 1

You may see both single schema and double schema examples and my workaround
to restore the database on my blog post:
https://lloyd.thealbins.com/Restoring%20a%20database%20with%20check%20constraints


Lloyd Albin
Fred Hutchinson Cancer Research Center

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> I have found an edge case where pg_dump and pg_restore fails to restore
> databases with check constraints that points to data in another table that
> is restored after the table containing the check constraint.

This is an unsupported case.[1]  In general, table CHECK constraints are
only allowed to concern themselves with the data in the particular row
that they're checking.  While we can't actually stop you from cheating by
hiding the subquery in a function, we're not going to consider it a bug
that such a setup doesn't work reliably.

I'd suggest rethinking your data representation so that you can make the
tables' relationship into a regular foreign key constraint.  Those, the
system understands well enough to maintain in a dump/reload context.

            regards, tom lane

[1] Specifically, we disclaim support for SQL feature F671, "Subqueries
in CHECK constraints".