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