Re: inheritance - Mailing list pgsql-novice

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.

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
>