Thread: BUG #11381: Inherited NOT NULL to NULLABLE column: backup restore error.

The following bug has been logged on the website:

Bug reference:      11381
Logged by:          Martin Winkel
Email address:      m.winkel@w2solutions.nl
PostgreSQL version: 9.3.5
Operating system:   Ubuntu 14.04 X64
Description:

Dummy case to reproduce the error:

Create tables:

CREATE TABLE base
(
  id serial NOT NULL,
  name character varying(127) NOT NULL,
  nickname character varying(127) NOT NULL,
  email_address character varying(512) NOT NULL,
  useless_but_required_field text NOT NULL,
  CONSTRAINT base_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE inherited
(
  CONSTRAINT inherited_pkey PRIMARY KEY (id)
)
INHERITS (base)
WITH (
  OIDS=FALSE
);

ALTER TABLE inherited
   ALTER COLUMN useless_but_required_field DROP NOT NULL;


According to the documentation, it is not clear if this should work. But it
is allowed by version 9.3.5 on ubuntu X64.

In the inherited colum I can insert columns with
"useless_but_required_field" NULL.

The problem is, when I back-up this database using the pg_dump command, I
cannot restore it using pg_restore (or the PgAdmin GUI).

It gives me the following error:

DETAIL:  Failing row contains (1, Martin W, storeman, xx, null).
CONTEXT:  COPY inherited, line 1: "1    Martin W    storeman    xx    \N

Two solutions possible (as far as i can see):
1. Don't allow for children to be nullable if parent isn't
2. Update pg_dump/pg_restore to work with the nullable child columns

m.winkel@w2solutions.nl writes:
> CREATE TABLE base
> (
>   id serial NOT NULL,
>   name character varying(127) NOT NULL,
>   nickname character varying(127) NOT NULL,
>   email_address character varying(512) NOT NULL,
>   useless_but_required_field text NOT NULL,
>   CONSTRAINT base_pkey PRIMARY KEY (id)
> )
> WITH (
>   OIDS=FALSE
> );

> CREATE TABLE inherited
> (
>   CONSTRAINT inherited_pkey PRIMARY KEY (id)
> )
> INHERITS (base)
> WITH (
>   OIDS=FALSE
> );

> ALTER TABLE inherited
>    ALTER COLUMN useless_but_required_field DROP NOT NULL;

IMO this should definitely be disallowed.  But we lack the catalog
infrastructure to do so conveniently.  There has been some work
towards representing NOT NULL constraints more like CHECK constraints,
which do have the required infrastructure.  Once that's finished,
you'll probably see this be rejected.  There's not much point in
trying to make pg_dump cope with it, I think.

            regards, tom lane