Skip site navigation (1) Skip section navigation (2)

Re: REFERENCES misbehaves with inheritance

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Steve White <swhite(at)aip(dot)de>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: REFERENCES misbehaves with inheritance
Date: 2010-02-02 20:15:58
Message-ID: 603c8f071002021215l326f8e27h4ffeba9727db0df4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Sun, Jan 31, 2010 at 9:07 AM, Steve White <swhite(at)aip(dot)de> wrote:
> Hi,
>
> I'm aware that this is a manifestation of the problem mentioned in the
> Caveats subsection of the Inheritance section.  I want to emphasize it,
> and maybe rattle your cage a bit.
>
> I find the Postgres notion of inheritance very compelling. Conceptually
> it does what I want, when I create tables of related, but different kinds
> of things.
>
> Unfortunately these little ommissions really foul up implementations
> using inheritance.
>
> For instance:  a field that REFERENCES a field in an inherited table is
> unaware that records have been added to the inherited table, by way of
> records being added to inheriting tables.
>
> This is awful.  One is forced to make choices between various evils.
>
> EXAMPLE:
> ============================================================================
>
> CREATE TABLE a (
>  a_id   SERIAL PRIMARY KEY
> );
>
> CREATE TABLE a1 (
> ) INHERITS( a );
>
> CREATE TABLE a2 (
> ) INHERITS( a );
>
> CREATE TABLE b (
>  b_id   SERIAL PRIMARY KEY,
>  a_id   INTEGER,
>  FOREIGN KEY (a_id) REFERENCES a(a_id)
> );
> -- ---------------------------------------
>
> INSERT INTO a1 VALUES( DEFAULT );
>
> -- The following results in a foreign key violation, saying
> -- no row with a_id=1 is present in table "a":
> INSERT INTO b VALUES( DEFAULT, CURRVAL('a_a_id_seq') );
>
> -- However this indicates that table "a" has a row with a_id=1:
> SELECT * FROM a;

I am guessing that the problem with this feature is not so much that
it's hard to implement as that the performance could be terrible: no
one has gotten around to adding the ability to create an index that
includes both the parent and all of its inheritance children.

I suppose in theory if each child had an index on the relevant
column(s) it might not be too bad, for certain use cases, but if you
have, say, a thousand child tables and have to make an index probe
into each one for each row inserted into the referring table, that
could be pretty ugly (~2k random seeks per row - ouch).

...Robert

In response to

pgsql-bugs by date

Next:From: Jasen BettsDate: 2010-02-02 21:45:00
Subject: whole-row functional index?
Previous:From: Kelly SACAULTDate: 2010-02-02 20:11:04
Subject: BUG #5308: How to disable Case sensitivity on naming identifiers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group