Another unpleasant surprise using inheritance

From: Darko Prenosil <darko(dot)prenosil(at)finteh(dot)hr>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Another unpleasant surprise using inheritance
Date: 2004-06-11 12:11:00
Message-ID: 200406111411.00602.darko.prenosil@finteh.hr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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 !

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Kings-Lynne 2004-06-11 12:51:39 Re: [COMMITTERS] pgsql-server: Clean up generation of default
Previous Message Dave Cramer 2004-06-11 11:51:04 Re: Postgresql JDBC-Driver