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
