Обсуждение: REFERENCES misbehaves with inheritance

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;

============================================================================

Cheers!

--
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
| Steve White                                             +49(331)7499-202
| e-Science / AstroGrid-D                                   Zi. 35  Bg. 20
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

On Sun, Jan 31, 2010 at 9:07 AM, Steve White <swhite@aip.de> wrote:
> Hi,
>
> I'm aware that this is a manifestation of the problem mentioned in the
> Caveats subsection of the Inheritance section. =A0I 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: =A0a 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. =A0One is forced to make choices between various evils.
>
> EXAMPLE:
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>
> CREATE TABLE a (
> =A0a_id =A0 SERIAL PRIMARY KEY
> );
>
> CREATE TABLE a1 (
> ) INHERITS( a );
>
> CREATE TABLE a2 (
> ) INHERITS( a );
>
> CREATE TABLE b (
> =A0b_id =A0 SERIAL PRIMARY KEY,
> =A0a_id =A0 INTEGER,
> =A0FOREIGN 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=3D1 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=3D1:
> 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