Thread: 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
Hi, i've posted various problems regarding inheritance in postgres, Never see a reply :( Well, beside the referential constraint issue ( that can be solved by hand ), the most frustrating is that queries like DELETE FROM table* where key = some_value Fails ( parser error ). ( UPDATE either ) I you have some direct contact with the developers please forward this issue. Thanx. Danilo Gonzalez Hashimoto writes: > 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 >
>
> Hi, i've posted various problems regarding inheritance in postgres,
> Never see a reply :(
>
> Well, beside the referential constraint issue ( that can be solved by
> hand ), the most frustrating is that queries like
>
> DELETE FROM table* where key = some_value
>
> Fails ( parser error ). ( UPDATE either )
>
> I you have some direct contact with the developers please forward this
> issue.
Are you sure about this? I see all sorts of inheritance updates in the
regression tests:
UPDATE a SET aa='zzzz' WHERE aa='aaaa';
UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
UPDATE b SET aa='zzz' WHERE aa='aaa';
UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>> Well, beside the referential constraint issue ( that can be solved by
>> hand ), the most frustrating is that queries like
>> DELETE FROM table* where key = some_value
>> Fails ( parser error ). ( UPDATE either )
Not as of 7.1.
regards, tom lane
Tom Lane writes: > >> Well, beside the referential constraint issue ( that can be solved by > >> hand ), the most frustrating is that queries like > >> DELETE FROM table* where key = some_value > >> Fails ( parser error ). ( UPDATE either ) > > Not as of 7.1. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Yeah, GREAT. 7.1.2 works fine !!! The buggy version is 7.0.3 Please keep this really COOL thing working in future versions. THANX. -- ______________________________________________________ Felipe Alvarez Harnecker. QlSoftware. Tels. 665.99.41 - 09.874.60.17 e-mail: felipe.alvarez@qlsoft.cl http://qlsoft.cl/ http://ql.cl/ ______________________________________________________