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 ancestor

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 constraint "reference_ancid_fkey"
-- DETAIL: Key (ancid)=(2) is not present in table "ancestor".

Bye,

Gergely Czuczy
mailto: gergely(dot)czuczy(at)harmless(dot)hu

--
Weenies test. Geniuses solve problems that arise.