Bad interaction between pg_dump/pg_restore and table inheritance

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