Thread: 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@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

degtyaryov@gmail.com writes:
> $ 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

[ scratches head... ]  When I do that, it refuses to insert into t2
either:

regression=# create table t1(a integer not null, b integer);
CREATE TABLE
regression=# create table t2(a integer, b integer) inherits (t1);
NOTICE:  merging column "a" with inherited definition
NOTICE:  merging column "b" with inherited definition
CREATE TABLE
regression=# insert into t1(a,b) values(null,1);
ERROR:  null value in column "a" violates not-null constraint
DETAIL:  Failing row contains (null, 1).
regression=# insert into t2(a,b) values(null,1);
ERROR:  null value in column "a" violates not-null constraint
DETAIL:  Failing row contains (null, 1).

This is the behavior I would expect, and I see it in all active
branches.  Have you modified the code around column inheritance?

            regards, tom lane

Дмитрий Дегтярёв <degtyaryov@gmail.com> writes:
> However, in accordance with the documentation
> http://www.postgresql.org/docs/9.4/static/ddl-inherit.html(quote: "It must
> also include check constraints with the same names and check expressions as
> those of the parent."),
> alter table t2 alter column a drop not null;
> the result must be a error.

Ah.  Yeah, that's a known issue: NOT NULL constraints don't currently have
enough infrastructure for ALTER TABLE to realize whether they're inherited
or not.  The system should indeed prevent you from doing DROP NOT NULL
here, but it doesn't.  I think there's an item for that on the TODO list.
        regards, tom lane