BUG #12946: pg_dump/pg_restore not restore data for inherit tables

The following bug has been logged on the website:

Bug reference: 12946
Logged by: Dmitriy
Email address: degtyaryov(at)gmail(dot)com
PostgreSQL version: 9.4.1
Operating system: Fedora 21
Description:

Hello.

There was an incident.

I perform
1. pg_dump - succesful
2. dropdb - succesful
3. createdb - succesful
4. pg_restore - failure

It was so.

# case 1
$ sudo su - postgres
$ createdb -O test -e -E UTF-8 test_inherits
CREATE DATABASE test_inherits OWNER test ENCODING 'UTF-8';
$ psql -U test -d test_inherits
test_inherits=> create table t1(a integer not null, b integer);
CREATE TABLE
test_inherits=> create table t2(a integer, b integer) inherits (t1);
CREATE TABLE
test_inherits=> insert into t1(a,b) values(null,1);
ERROR: null value in column "a" violates not-null constraint
DETAIL: Failing row contains (null, 1).
test_inherits=> insert into t2(a,b) values(null,1);
INSERT 0 1
test_inherits=> \q
$ pg_dump -U test -d test_inherits -F c > test.sql
$ dropdb test_inherits
$ createdb -O test -e -E UTF-8 test_inherits
CREATE DATABASE test_inherits OWNER test ENCODING 'UTF-8';
$ pg_restore -d test_inherits -F c test.sql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2912; 0 37803 TABLE DATA t2
test
pg_restore: [archiver (db)] COPY failed for table "t2": ERROR: null value
in column "a" violates not-null constraint
DETAIL: Failing row contains (null, 1).
CONTEXT: COPY t2, line 1: "\N 1"
WARNING: errors ignored on restore: 1
$ dropdb test_inherits

# case 2
$ sudo su - postgres
$ createdb -O test -e -E UTF-8 test_inherits
CREATE DATABASE test_inherits OWNER test ENCODING 'UTF-8';
$ psql -U test -d test_inherits
test_inherits=> create table t1(a integer not null, b integer);
CREATE TABLE
test_inherits=> create table t2() inherits (t1);
CREATE TABLE
test_inherits=> alter table t2 alter column a drop not null;
ALTER TABLE
test_inherits=> insert into t1(a,b) values(null,1);
ERROR: null value in column "a" violates not-null constraint
DETAIL: Failing row contains (null, 1).
test_inherits=> insert into t2(a,b) values(null,1);
INSERT 0 1
test_inherits=> \q
$ pg_dump -U test -d test_inherits -F c > test.sql
$ dropdb test_inherits
$ createdb -O test -e -E UTF-8 test_inherits
CREATE DATABASE test_inherits OWNER test ENCODING 'UTF-8';
$ pg_restore -d test_inherits -F c test.sql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2912; 0 37803 TABLE DATA t2
test
pg_restore: [archiver (db)] COPY failed for table "t2": ERROR: null value
in column "a" violates not-null constraint
DETAIL: Failing row contains (null, 1).
CONTEXT: COPY t2, line 1: "\N 1"
WARNING: errors ignored on restore: 1
$ dropdb test_inherits

# case 3
$ createdb -O test -e -E UTF-8 test_inherits
CREATE DATABASE test_inherits OWNER test ENCODING 'UTF-8';
$ psql -U test -d test_inherits
test_inherits=> create table t1(a integer not null, b integer);
CREATE TABLE
test_inherits=> create table t2(a integer not null, b integer);
CREATE TABLE
test_inherits=> alter table t2 inherit t1;
ALTER TABLE
test_inherits=> alter table t2 alter column a drop not null;
ALTER TABLE
test_inherits=> insert into t1(a,b) values(null,1);
ERROR: null value in column "a" violates not-null constraint
DETAIL: Failing row contains (null, 1).
test_inherits=> insert into t2(a,b) values(null,1);
INSERT 0 1
$ pg_dump -U test -d test_inherits > test.sql
$ dropdb test_inherits
$ createdb -O test -e -E UTF-8 test_inherits
CREATE DATABASE test_inherits OWNER test ENCODING 'UTF-8';
$ pg_restore -d test_inherits -F c test.sql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2912; 0 37803 TABLE DATA t2
test
pg_restore: [archiver (db)] COPY failed for table "t2": ERROR: null value
in column "a" violates not-null constraint
DETAIL: Failing row contains (null, 1).
CONTEXT: COPY t2, line 1: "\N 1"
WARNING: errors ignored on restore: 1
$ dropdb test_inherits