PostgreSQL Version: PostgreSQL 9.4.1, compiled by Visual C++ build 1800,
64-bit
OS Version: Windows 7 Professional, SP 1
Short Description:
Bad interaction between pg_dump/pg_restore and table inheritance
Details:
Using pg_dump/pg_restore can lead to changes in a constraint definition such
that inheritance no longer works.
The following steps (see below for SQL statements) may be followed to
reproduce the problem. The basic idea is:
1. Create two tables (test_master and test_sub) that have the same
column names/data types and the same check constraint definition.
2. Alter test_sub to inherit from test_master and verify that a SELECT
on the test_master table works.
3. Use pg_dump to export the schema containing the two tables
4. Use pg_restore to restore the schema
5. Recreate the test_sub table
6. Attempt to alter test_sub to inherit from test_master. The ALTER
statements fails with the following error;
ERROR: child table "test_sub" has different definition for check constraint
"yn_check"
DROP SCHEMA IF EXISTS test_check CASCADE;
CREATE SCHEMA test_check;
CREATE TABLE test_check.test_master (
descript VARCHAR(10),
yn VARCHAR(1),
CONSTRAINT yn_check CHECK (yn IN ('Y', 'N')));
DROP TABLE IF EXISTS test_check.test_sub;
CREATE TABLE test_check.test_sub (
descript VARCHAR(10),
yn VARCHAR(1),
CONSTRAINT yn_check CHECK (yn IN ('Y', 'N')));
INSERT INTO test_check.test_sub (descript, yn) VALUES ('descript1', 'Y');
ALTER TABLE test_check.test_sub INHERIT test_check.test_master;
-- Verify the query returns the expected result of one row
SELECT * FROM test_check.test_master;
-- Export the test_check schema
-- C:\Program Files (x86)\pgAdmin III\1.20\pg_dump.exe --host localhost
--port 5432 --username "ldx" --no-password --format custom --verbose --file
"C:\Temp\test_check.backup" --schema "test_check" "ldx"
-- Rename the original schema
ALTER SCHEMA test_check RENAME TO test_check_orig;
-- Create a new schema named test_check
CREATE SCHEMA test_check;
-- Import the data from the backup file
-- C:\Program Files (x86)\pgAdmin III\1.20\pg_restore.exe --host localhost
--port 5432 --username "ldx" --dbname "ldx" --no-password --schema
test_check --verbose "C:\Temp\test_check.backup"
-- DROP the imported version of the test_sub table and recreate it
DROP TABLE IF EXISTS test_check.test_sub;
CREATE TABLE test_check.test_sub (
descript VARCHAR(10),
yn VARCHAR(1),
CONSTRAINT yn_check CHECK (yn IN ('Y', 'N')));
INSERT INTO test_check.test_sub (descript, yn) VALUES ('descript1', 'Y');
-- Attempt to set up the inherit relationship with test_master
ALTER TABLE test_check.test_sub INHERIT test_check.test_master;
-- The ALTER TABLE statement returns the following error
-- ERROR: child table "test_sub" has different definition for check
constraint "yn_check"
-- SQL state: 42804
-- Here is the DDL for the original version ot the test_master table:
CREATE TABLE test_check_orig.test_master
(
descript character varying(10),
yn character varying(1),
CONSTRAINT yn_check CHECK (yn::text = ANY (ARRAY['Y'::character varying,
'N'::character varying]::text[]))
)
-- Here is the DDL for the version of the table imported from a schema
backup
CREATE TABLE test_check.test_master
(
descript character varying(10),
yn character varying(1),
CONSTRAINT yn_check CHECK (yn::text = ANY (ARRAY['Y'::character
varying::text, 'N'::character varying::text]))
)
Kind regards,
Rich