Re: Integrity and Inheritance

On 10 Jun 2001, Christophe Labouisse wrote:

> I wanted to build the following schema :
>
> - one "generic" document table with a column doc_id ;
> - a couple of "specific" document tables inheriting from doc ;
> - a table refering a document by it's id with and integrity constraint
> on it.
>
> In SQL :
>
> CREATE TABLE doc (
> doc_id serial PRIMARY KEY,
> );
>
> CREATE TABLE lexique (
> ) INHERITS (doc);
>
> CREATE TABLE word_doc (
> id serial PRIMARY KEY,
> doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE,
> );
>
>
> What I tried to do next is to insert a new "lexique" entry, and then a
> bunch of "word_doc" rows refering this entry. The last part fails with
> a constraint violation this is quite normal since the trigger in
> backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY".
>
> What should I do ? Should I consider another way to do what I want,
> rewrite a trigger to replace the system one ?

One problem is that for update isn't supported across inheritance trees
AFAICS and the triggers use for update for the appropriate locking. If
you were to write a trigger that worked across the tree, you'd probably
need to take that into account for the locking.

You might be best off not using inheritance, and giving the "subtables"
ids that refer back to a separate doc_id table that keeps the ids. This
has the downside of requiring a separate write for the doc_id and document
tables, but means that you don't hit any of the wierdness surrounding
inheritance.