Thread: Documentation Bug related to Inheritance

Documentation Bug related to Inheritance

From
Danilo Gonzalez Hashimoto
Date:
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

Documentation Bug related to Inheritance

From
Felipe Alvarez Harnecker
Date:
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
 >

Re: Documentation Bug related to Inheritance

From
Bruce Momjian
Date:
>
> 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

Re: Documentation Bug related to Inheritance

From
Tom Lane
Date:
>> 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

Re: Documentation Bug related to Inheritance

From
Felipe Alvarez Harnecker
Date:
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/
______________________________________________________