Re: inheritance - Mailing list pgsql-novice

Ben Clewett wrote:

> PostgreSQL,
>
> I've been following with interest the discussions on Inheritance, as
> we are planning to use this.
>
> There may be alternate methods.  I understand that a view on a master
> table gives the same abilities.  Although I have to wonder if after
> multiple inheritance in both breadth and depth, whether the view
> method would suffer very large master table and excessive maintenance
> issues. But mainly the simplicity, and analogy to the OO techniques we
> are using in code design, make this attractive.  Looking specifically
> at transactional relations, which have many foreign keys and specific
> data for each instance of use.  Inheriting the parent relation for a
> new type of transaction as and when required, is appealing.

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)...

But the point is that implementing the same complicated and hairy
structure using the "real inheritance" will actually result in the same
(if not worse) performance problems (you'll end up having all those
tables, sitting around, that need to be sync'd with triggers after every
statement you make) - only those problems will be much harder to spot,
because of lots of stuff going on behind the scenes ...

For example,  the simplest possible query like 'select * from
the_superclass_table' will get translated into something like...
select * from the_superclass_table union all
select ... from the_first_subclass union all
select ... from another_subclass union all
select ... from subclass_of_the_first_subclass union all
... etc, etc, etc...

which just has to be slow by itself, not to mention the extra time,
spent by the query planner to sort through all of your inheritance tree
and figure out what you want to get from where...
... and if you (God forbid!) have multiple inheritance, that will become
even more nightmare - having to straighten out the loops, eliminate the
duplicates, resolve the confilcts, blah, blah, blah... *every time* you
do any simple select ...

The point is, that if you avoid the "object-oriented" style, you'll see
(most of) these problems right away, when designing your schema, and
will be able to avoid them, by restructuring your data, and optimizing
your schema for performance.

Dima

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

select * from a;

 id | name
----+------
  1 | dima
  2 | dima
(2 rows)

Does it make any sense at all ???? Well... not to me, it doesn't.


>
> I simply want to know whether it's a bug that the ancestor relations
> may have duplicate entries on its PK, or just an unfortunate side
> effect of inheritance?
>
> Thanks, Ben.
>
>
> Dmitry Tkach wrote:
>
>> Jake Stride wrote:
>>
>>> Doing things your way means that you end up having several different
>>> views and sets of data to look after on updates etc.
>>>
>> Sure. And doing it "your way" means that you'll end up having several
>> different tables, instead ov several different views. :-)
>> I don't really see why having different views is any worse than
>> having different tables ... if anything, it is better, because you do
>> not
>> have to duplicate all the data - it still sits all on one master set.
>>
>>> You don't have to worry about rules and extra data, postgreSQL looks
>>> after everything for you.
>>
>>
>> You only don't have to worry about extra data as long as fits on your
>> hard drive, and maintanance (such as vacuuming it) doesn't take
>> weeks, otherwise you very much do (have to worry about it)
>>
>>> With the inheritance, anything you insert into
>>> the capitals table automatically appears in the the cities table
>>> too, so
>>> do all updates.
>>>
>> Yes. But the only thing you "save" by using inheritance is, that
>> instead of having to 'create rule ... as on insert...' you do 'create
>> table ... inherits...'.
>>
>>>
>>> Hope this explains :-)
>>>
>>> As to duplicate keys, you can recreate the primary key in each
>>> inherited
>>> table. Also shouldn't you be updating in the relavent table?
>>>
>> Yeah... so much for "postgreSQL looks after everything for you" :-)
>>
>> Dima
>>
>>>
>>> On Wed, 2003-07-09 at 16:54, Dmitry Tkach wrote:
>>>
>>>
>>>> Perhaps, I am missing something in this whole inheritance
>>>> picture... But this is exactly one (of many) reasons why I could
>>>> never understand why people even consider using anything like
>>>> inheritance in sql :-)
>>>> Perhaps, somebody could explain to me what kind of benefit you can
>>>> get from this 'inheritance' thing, that would outweight the obvious
>>>> disadvantages (such as this problem, a similar problem with
>>>> unique/foreign keys,  data duplication etc...).
>>>>
>>>> For this particular case, I would do something like this, rather
>>>> than messing with inheritance:
>>>>
>>>> create table cities
>>>> (
>>>>   name text primary key,
>>>>   population float,
>>>>   altitude int,
>>>>   capital bool not null default false
>>>> );
>>>>
>>>> create view capitals as select name, population, altitude from
>>>> cities where capital;
>>>> create rule new_capital as on insert to capitals do instead insert
>>>> into cities values (new.*, true);
>>>> create rule upd_capital as on update to capitals do instead update
>>>> cities set name=new.name, population=new.population,
>>>> altitude=new.altitude where name=old.name;
>>>>
>>>> -- plus, perhaps, a partial index to speed up getting a list of all
>>>> capitals if necessary:
>>>> create unique index capital_idx on cities (name) where capital;
>>>>
>>>> Dima
>>>>
>>>> Volker Krey wrote:
>>>>
>>>>
>>>>
>>>>> Hello,
>>>>>
>>>>> I am working with PostgreSQL 7.2.1 under Windows 2000 (native
>>>>> version by PeerDirect) and have a problem with inheritance. To
>>>>> illustrate it, I will take the inheritance example from the Users
>>>>> Guide with a minor change, i.e. I introduce a PRIMARY KEY to the
>>>>> original table cities.
>>>>>
>>>>> CREATE TABLE cities (
>>>>>     name            text PRIMARY KEY,
>>>>>     population      float,
>>>>>     altitude        int     -- (in ft)
>>>>> );
>>>>>
>>>>> CREATE TABLE capitals (
>>>>>     state           char(2)
>>>>> ) INHERITS (cities);
>>>>>
>>>>> My problem now is the following: If I insert a data set into
>>>>> capitals, everything looks fine and a SELECT on cities returns the
>>>>> appropriate data just inserted into capitals. But if I now insert
>>>>> a city with the same name into cities the system will accept it so
>>>>> that I find myself with two entries in cities that have the same
>>>>> PRIMARY KEY. Of course this causes trouble, e.g. if I want to
>>>>> UPDATE one entry, an error message appears. If I still insist on
>>>>> changing the entry, both will be affected, because they share the
>>>>> same PRIMARY KEY.
>>>>> Can anybody tell me how to solve this problem? Maybe it has
>>>>> already been solved and is just a result of me using the old 7.2.1
>>>>> version. I'd be very grateful for any hints, since the inheritance
>>>>> features of PostgreSQL would make life a lot easier for me.
>>>>>
>>>>> Thanks for your help, Volker.
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 8: explain analyze is your friend
>>>>
>>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org