fkey+inheritance issue

Hello

i had written a bug report on the web-based for, but
it hadn't accepted my email address, so i'm trying to
report this here.

PostgreSQL version:
PostgreSQL 8.2.0 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4=
 [FreeBSD] 20050518

OS: FreeBSD 6.1-RELEASE-p10

Short descr:
fkey cannot reference to an inherited table's child's tuple through the anc=
estor

Details:
hen given an inherited table structure and one other table's FKEY uses the =
supertable's inherited PKEY as a reference, it gets an error when it comes =
to child table's tuples.

whereas in sql-createtable.html it's said that then children's data can be =
accessed through the parent table(s).

the following code demonstrates the issue well:


CREATE TABLE "ancestor" (
        "id"            integer         NOT NULL,
        "name"          varchar(32)     NOT NULL,
        PRIMARY KEY     ("id")
);

CREATE TABLE "child" (
        "attr1"         integer         NOT NULL
) INHERITS ("ancestor");

CREATE TABLE "reference" (
        "id"            integer         NOT NULL,
        "ancid"         integer         NOT NULL,
        PRIMARY KEY     ("id"),
        FOREIGN KEY     ("ancid")       REFERENCES      "ancestor"("id")
);

INSERT INTO "ancestor" (id,name) VALUES (1,'ancfoo');
INSERT INTO "child" (id,name,attr1) VALUES (2,'childfoo', 2);
INSERT INTO "reference" (id,ancid) VALUES (1,1);
-- till here it's OK
INSERT INTO "reference" (id,ancid) VALUES (2,2);
-- here:
-- ERROR:  insert or update on table "reference" violates foreign key const=
raint "reference_ancid_fkey"
-- DETAIL:  Key (ancid)=3D(2) is not present in table "ancestor".


Bye,

Gergely Czuczy
mailto: gergely.czuczy@harmless.hu

--=20
Weenies test. Geniuses solve problems that arise.