Another unpleasant surprise using inheritance

Started by Darko Prenosilover 21 years ago2 messages
#1Darko Prenosil
darko.prenosil@finteh.hr

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 DEFAULT 0,
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 not present 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 !

#2Manfred Koizar
mkoi-pg@aon.at
In reply to: Darko Prenosil (#1)
Re: Another unpleasant surprise using inheritance

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.

Servus
Manfred