Re: inheritance - Mailing list pgsql-novice

Ben Clewett wrote:

> Dmitry,
>
> Dmitry Tkach wrote:
>
>> You are right - a complicated and hairy 'inheritance' structure,
>> emulated with views will result in some inefficiencies (although 'a
>> very large master table' is not a problem at all - it is generally
>> better from peformance standpoint to have one large table than many
>> smaller ones, especially when those smaller tables duplicate the
>> content of each other, as it is the case with inheritance)...
>
>
>
> Final extension of this idea would be the true OO methodology of
> having a single relation from whence all others are untimatelly
> inherited.  Not an idea I would use, but has advantages.  In this
> case, using VIEW method, entire database would be just one table.  Are
> you saying this is better on performace?

Generally, yes., provided that it is properly designed and indexed. It
is quicker to query one table than to union the results of several
subqueries together.
The only problem with merging *entire* database into the same table is
that the table's row size will become excessive, so that you won't be
able to fit many rows on the page... That is why the common databses are
called *relational* - it doesn't have to be *one* table - you can split
the *columns* (not *rows*, as you do with 'inheritance') into several
logical subsets, and put those into separate tables, connected with
foreign keys.


>
> Real advantages I can personally use today:  We have many relations
> with common index structure, some common fields and some common
> foreign keys.  The ability to inherite all these from a template
> relation is very useful...  Even if parent table is never used.

First, indices are *not* inherited:

Something like
create table a (id int primary key);
create table b (name text) inherits a;

select * from b where id=1;

will use a seq scan on b.
You would have to create the index explicitly if you needed it.

Second, templates are good for sql code development and maintanance, but
do not really matter to the real database schema.
If templates is all you want from 'inheritance', just use C-style
macros, and pipe your sql code through a C preprocessor when you create
the schema.

>
> I do not know the methods used with Inheritance.  Whether the data is
> duplicated through the ancestoral structure, or referenced.  (Would be
> a nice configurable option?)  If your saying all data in
> superclass_table is only UNION'd at run time, then I totally agree,
> big performace hit!
>
It would be a huge performance hit *either* way.
If it was duplicated into every superclass, I doubt the performance
would be any better (seects could, in some instanses be quicker, but
inserts/updates would become real nightmare)

>
>> P.S. BTW, you mentioned foreign keys... They don't work with
>> inheritance either (just like unique constraints don't - see my
>> earlier message)
>> For example:
>>
>> create table a (id int primary key, name text);
>> create table b (last_name text) inherits (a);
>> create table c (id int references (a), data text);
>>
>> insert into a values (1, 'dima');
>> insert into b values (2, 'dima', 'tkach');
>>
>> insert into c values (1, 'This works');
>> insert into c values (2, 'BUT THIS DOES NOT!');
>>
>> ERROR:  $1 referential integrity violation - key (id)=(2) referenced
>> from c not found in a
>
>
> Surelly this is correct and expected?  the 'DOES NOT!' value refereces
> a value which truelly does not exist.  It's not in the 'c' relation,
> only in it's parent 'a'.


You got it wrong. It is not supposed to exist in c (I am just
*inserting* it there). It needs to exist in a - see what the error
message says - id=2 not found in *a*.
You can then try select * from a; It will return a row with id=2. The
reason why the constraint fails is that this row is not *really* in a -
it was inserted into b, and, according to the inheritance rules is
visible throuhg a, *but* the constraint doesn honor those rules and only
checks what is physically in a, and fails.

It can be argued whether this is the "correct " behaviour or not, and
what the standards have to say about it, but my point is, whether or not
it is "correct", it seems to make inheritance totally useless in this
(as well as many others) situations:

- PK's are not inherited,
- FK's are not inherited
- Unique constraints are not inherited
- Triggers are not inherited
- Rules are not inherited

Pretty much, *nothin* is really inherited, except for column names...
Well, to emulate *this*, you'd be really *much * better off simply using
macros in your sql code - that would have no prefromance hit at all (and
would provide more functionality, as you could inherit whatever you want)...


Dima