Collected from the PG bugs email list.
|Reported by||Lloyd Albin|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15316 Logged by: Lloyd Albin Email address: (redacted) 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
|2018-08-08 00:04:53+00||=?utf-8?q?PG_Bug_reporting_form?=||BUG #15316: pg_restore failes to load data that is constrainted by a functional check constraints|
|2018-08-08 02:53:49+00||Tom Lane||Re: BUG #15316: pg_restore failes to load data that is constrainted by a functional check constraints|