Обсуждение: BUG #5555: ALTER TABLE ONLY ... SET NOT NULL on parent prevent prior inherited tables from being restored

The following bug has been logged online:

Bug reference:      5555
Logged by:          Michael Glaesemann
Email address:      grzm@seespotcode.net
PostgreSQL version: 8.4.4
Operating system:   Mac OS X
Description:        ALTER TABLE ONLY ... SET NOT NULL on parent prevent
prior inherited tables from being restored
Details:

Due to how inheritance handles NOT NULL constraints, you can get yourself
into a situation where a dumped database can't be restored.

If you add a column to a parent table with no NOT NULL constraint and then
add the NOT NULL constraint on the parent using ALTER TABLE ONLY (so only
future tables inheriting from the parent have the constraint), the dump file
does not indicate that those children created prior to the ALTER TABLE ONLY
have no NOT NULL constraint.

This is true for both 8.3 and 8.4. (I haven't tested earlier versions.)
Here's a test case:

CREATE TABLE parents (parent_name TEXT NOT NULL UNIQUE);
CREATE TABLE gen_1 () INHERITS (parents);
INSERT INTO gen_1 (parent_name) VALUES ('adam');
ALTER TABLE parents ADD COLUMN new_col TEXT;
ALTER TABLE ONLY parents ALTER new_col SET NOT NULL;
CREATE TABLE gen_2 () INHERITS (parents);

inherits_test=# \d parents
     Table "public.parents"
   Column    | Type | Modifiers
-------------+------+-----------
 parent_name | text | not null
 new_col     | text | not null
Indexes:
    "parents_parent_name_key" UNIQUE, btree (parent_name)

inherits_test=# \d gen_1
      Table "public.gen_1"
   Column    | Type | Modifiers
-------------+------+-----------
 parent_name | text | not null
 new_col     | text |
Inherits: parents

inherits_test=# \d gen_2
      Table "public.gen_2"
   Column    | Type | Modifiers
-------------+------+-----------
 parent_name | text | not null
 new_col     | text | not null
Inherits: parents

inherits_test=# INSERT INTO gen_1 (parent_name) VALUES ('eve');
INSERT 0 1
inherits_test=# INSERT INTO gen_2 (parent_name) VALUES ('cain'); -- errors
out as expected
ERROR:  null value in column "new_col" violates not-null constraint
inherits_test=# INSERT INTO gen_2 (parent_name, new_col) VALUES ('cain', 'up
to no good');
INSERT 0 1
inherits_test=# \q
$ pg_dump inherits_test > inherits_test-8.4.sql
$ createdb inherits_test_restore
$ psql -d inherits_test_restore -f inherits_test-8.4.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
psql:inherits_test-8.4.sql:59: ERROR:  null value in column "new_col"
violates not-null constraint
CONTEXT:  COPY gen_1, line 1: "adam    \N"
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT

"Michael Glaesemann" <grzm@seespotcode.net> writes:
> If you add a column to a parent table with no NOT NULL constraint and then
> add the NOT NULL constraint on the parent using ALTER TABLE ONLY (so only
> future tables inheriting from the parent have the constraint), the dump file
> does not indicate that those children created prior to the ALTER TABLE ONLY
> have no NOT NULL constraint.

Yeah.  Past discussions of this have leaned to the viewpoint that we
should disallow the above, ie it should not be possible to have NOT NULL
on the parent unless all the children have it too.  Nobody's got round
to implementing it though.  There's a TODO item for it: see
    "Move NOT NULL constraint information to pg_constraint"

            regards, tom lane

--On 12. Juli 2010 14:39:19 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Yeah.  Past discussions of this have leaned to the viewpoint that we
> should disallow the above, ie it should not be possible to have NOT NULL
> on the parent unless all the children have it too.  Nobody's got round
> to implementing it though.  There's a TODO item for it: see
>     "Move NOT NULL constraint information to pg_constraint"


FYI, i'm currently working on it, see

<https://commitfest.postgresql.org/action/patch_view?id=312>


        Bernd