Re: inheritance - Mailing list pgsql-novice

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