Thread: Another unpleasant surprise using inheritance

Another unpleasant surprise using inheritance

From
Darko Prenosil
Date:
I think I found bug related to table inheritance (or at least very weird
behavior).
Here is simplified example:
DROP SCHEMA master CASCADE;DROP SCHEMA skladisno CASCADE;CREATE SCHEMA master;CREATE SCHEMA skladisno;
CREATE TABLE master.analiticki_subjekti (    id serial NOT NULL PRIMARY KEY,    naziv varchar(60) NOT NULL UNIQUE);
CREATE TABLE master.partneri(    djelatnost text,    napomene text,    ziro_racun varchar(64)) INHERITS
(master.analiticki_subjekti);
INSERT INTO master.partneri     (id,naziv)VALUES    (0,'Fooo');

CREATE TABLE skladisno.skladista (    id int8 NOT NULL UNIQUE,    naziv text NOT NULL,    id_subjekta int NOT NULL
DEFAULT0,FOREIGN KEY (id_subjekta) REFERENCES master.analiticki_subjekti(id) ON DELETE  
RESTRICT ON UPDATE RESTRICT);
INSERT INTO skladisno.skladista(id,naziv,id_subjekta) VALUES (1,'Skladište
1',0);


Gives error:insert or update on table "skladista" violates foreign key constraint "$1"DETAIL:  Key (id_subjekta)=(0) is
notpresent in table "analiticki_subjekti". 
This is not true, because there is record in master.analiticki_subjekti with
id set to 0 (this record is inserted into master.partneri), but is clearly
visible when execute SELECT * FROM master.nalaiticki_subjekti.

Now, if I only change script from:INSERT INTO master.partneri     (id,naziv)VALUES    (0,'Fooo');
to:INSERT INTO master.analiticki_subjekti    (id,naziv)VALUES    (0,'Fooo');

insert passes without error.

Regards !


Re: Another unpleasant surprise using inheritance

From
Manfred Koizar
Date:
On Fri, 11 Jun 2004 14:11:00 +0200, Darko Prenosil
<darko.prenosil@finteh.hr> wrote:
>I think I found bug related to table inheritance (or at least very weird 
>behavior). 

This is well known and there's a todo for it:

# Allow inherited tables to inherit index, UNIQUE constraint, and
primary key, foreign key [inheritance] 

See also http://momjian.postgresql.org/cgi-bin/pgtodo?inheritance.

ServusManfred