Documentation Bug related to Inheritance

Inheritance is one of the most useful and 'marketed' features in PostgreSQL.
However its behavior is not what one expected it to be. Inherited tables do
not inherit some constraints, including primary and foreing keys.
This information is very important, specially for newbies, who can get very
frustated by referential integrity violation happening when they would not
be expected to happen, and vice versa.
The mailing lists have many questions related to it, including:

http://fts.postgresql.org/db/mw/msg.html?mid=118834
where Peter Eisentraut remommends one NOT TO mix inheritance
and foreing keys;
http://fts.postgresql.org/db/mw/msg.html?mid=1022481
where Stephan Szabo advices the reader to see past discussion in the
GENERAL list for more information
and http://fts.postgresql.org/db/mw/msg.html?mid=122007
where Bruce Momjian adds this issue to the TODO list.

Looking for past discussions in the lists require too much work, since this
is a very popular question. ( you find a lot of questions and answers, but
those only tells the reader 'Currently you can't do that' )
I would suggest this question (referential integrity in inherited tables) to
be added to the FAQ, since in think it is a bug in the documentation.
Perhaps it would also be useful a documentation page (probably the inheritance
one - http://www.postgresql.org/idocs/index.php?inherit.html) warning the
reader about this particular issue.
Additionally, this page should help the user to work through it telling how
to use inheritance and primary keys, using the current postgreSQL features
(rules, triggers, inheritance itself, etc.) or the 'pure' referential model.

A simple example of the problem follows:

CREATE TABLE student (
id serial,
name varchar(35),
email varchar(25),
UNIQUE ( email ),
PRIMARY KEY ( id ) );

CREATE TABLE graduate (
thesisname varchar (100)
) INHERITS ( student );

CREATE TABLE address (
student int4 REFERENCES student,
add1 varchar(50),
add2 varchar(50)
);

testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Danilo', 'danilogh@comp' );
INSERT 28259 1

-- *************[ Cannot duplicate UNIQUE value, as expected ]*****************
testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Another Danilo', 'danilogh@comp' );
ERROR: Cannot insert a duplicate key into unique index student_email_key

-- *************[ Should stop duplicating UNIQUE value, defined in student ]***
-- *************[ Strange behavior: must be documented ]***********************
testeinh=> INSERT INTO graduate ( name, email ) VALUES ( 'Yet Anothe Danilo', 'danilogh@comp' );
INSERT 28261 1

-- *************[ Duplicated UNIQUE field: 'broken' referential integrity ]****
testeinh=> SELECT * FROM student*;
id | name | email
----+-------------------+---------------
1 | Danilo | danilogh@comp
3 | Yet Anothe Danilo | danilogh@comp
(2 rows)

-- *************[ Referential integrity tested, and OK ]*********************
testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 1, 'Some Street' );
INSERT 28262 1

-- *************[ No id=2 in table: normal behavior ]*************************
testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 2, 'Some Other Street' );
ERROR: <unnamed> referential integrity violation - key referenced from address not found in student

-- *************[ Should allow inclusion, since id=3 exists for table inherited from student ]***********
-- *************[ Strange behavior: must be documented ]***********************
testeinh=> INSERT INTO address ( student, add1 ) VALUES ( 3, 'Some Other Street' );
ERROR: <unnamed> referential integrity violation - key referenced from address not found in student

Sorry my broken English, I'd be glad to explain better any confusing passage.
Regards,

Danilo Hashimoto

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html