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

Documentation Bug related to Inheritance

From: Felipe Alvarez Harnecker <felipe(at)qlsoft(dot)cl>
To: Danilo Gonzalez Hashimoto <danilogh(at)comp(dot)ufscar(dot)br>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Documentation Bug related to Inheritance
Date: 2001-07-20 18:54:18
Message-ID: 15192.32346.209236.970767@qlsoft.cl (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)comp' );
 > INSERT 28259 1
 > 
 > -- *************[ Cannot duplicate UNIQUE value, as expected ]*****************
 > testeinh=> INSERT INTO student ( name, email ) VALUES ( 'Another Danilo', 'danilogh(at)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(at)comp' );
 > INSERT 28261 1
 > 
 > -- *************[ Duplicated UNIQUE field: 'broken' referential integrity ]****
 > testeinh=> SELECT * FROM student*;
 >  id |       name        |     email
 > ----+-------------------+---------------
 >   1 | Danilo            | danilogh(at)comp
 >   3 | Yet Anothe Danilo | danilogh(at)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
 > 

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-07-20 20:41:06
Subject: Re: help for:FATAL 1: configuration file `postgresql.conf' has wrong permissions
Previous:From: Tom LaneDate: 2001-07-20 16:05:37
Subject: Re: check contraint allows illegal value?

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