BUG #12946: pg_dump/pg_restore not restore data for inherit tables - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      12946
Logged by:          Dmitriy
Email address:      degtyaryov@gmail.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