Skip site navigation (1) Skip section navigation (2)

BUG #5555: ALTER TABLE ONLY ... SET NOT NULL on parent prevent prior inherited tables from being restored

From: "Michael Glaesemann" <grzm(at)seespotcode(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5555: ALTER TABLE ONLY ... SET NOT NULL on parent prevent prior inherited tables from being restored
Date: 2010-07-12 16:11:38
Message-ID: 201007121611.o6CGBcks066260@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5555
Logged by:          Michael Glaesemann
Email address:      grzm(at)seespotcode(dot)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

Responses

pgsql-bugs by date

Next:From: Antonio Rivas ValleDate: 2010-07-12 17:08:33
Subject: Bad localization in psql (french version) for Windows
Previous:From: Tom LaneDate: 2010-07-12 14:27:09
Subject: Re: BUG #5552: incorrect returned value of time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group