Обсуждение: inheritance
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.
--
Volker Krey
v.krey@fz-juelich.de
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. >
Doing things your way means that you end up having several different views and sets of data to look after on updates etc. The example given means that you only have two tables and can quite easily see all the cities at once, and only the capitals if you want. You don't have to worry about rules and extra data, postgreSQL looks after everything for you. With the inheritance, anything you insert into the capitals table automatically appears in the the cities table too, so do all updates. 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? 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
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 >> >>
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 >
On Wed, 2003-07-09 at 17:56, 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. > > 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. > We use inheritence alonside OO techniques and it is very useful. The duplicate keys is (I beleive) because of the way the relationship is inherited using directed graphs. The way that we have overcome the problem is so: create table a (id bigserial, ...., primary key(id)); create table b (id bigserial, ..., primary key(id)) inherits (a); This means that you will always have a unique key in the inherited table as it 'replaces' the original id. Does this answer the question or am I off the mark? Jake
Jake Stride wrote: >... > >As to duplicate keys, you can recreate the primary key in each inherited >table. Also shouldn't you be updating in the relavent table? > > > Hello, I don't really get the idea of inheritance, what's 'recreating' a primary key? Let's say CITIES has as pk CITY_NUMBER as Serial. How do you recreate the primary key in CAPITALS? Would you create a integer 'pointing' the sequence of CITY_NUMBER? T.I.A. Pablo
> > > >The way that we have overcome the problem is so: > >create table a (id bigserial, ...., primary key(id)); > >create table b (id bigserial, ..., primary key(id)) inherits (a); > > I doubt this really overcomes the problem (at least, not the one, mentioned in the original post, and not the way I would call useful for real OO inheritance). For example: create table a (id int primary key, name text); create table b (id int primary key, last_name text); insert into a values (1, 'dima'); insert into b values (1, 'dima', 'tkach'); select * from a; id | name ----+------ 1 | dima 1 | dima Now, what kind of 'primary key' is this??? Dima >This means that you will always have a unique key in the inherited table >as it 'replaces' the original id. > >Does this answer the question or am I off the mark? > >Jake > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
This brings up a related question. What if a non-capital city becomes a capital? Maybe Illinois will decide to make Chicago its capital instead of Springfield :-) With Dmitry's method you just set the boolean flag. How do you do that with inherited tables? Do you have to delete the record from the cities table and create a new one in the capitals table? This will may break relationships. Avi On Wednesday, Jul 9, 2003, at 11:03 America/Chicago, 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. > > The example given means that you only have two tables and can quite > easily see all the cities at once, and only the capitals if you want. > > You don't have to worry about rules and extra data, postgreSQL looks > after everything for you. With the inheritance, anything you insert > into > the capitals table automatically appears in the the cities table too, > so > do all updates. > > 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? > > 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 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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
Thanks Jake.
I can see many places where both options would be useful. Having
duplicate PKs in parent, and stricktly unique parent PK. Depending on
the reason for the inhertiance. (Eg, useing a parent as a template for
children, when duplicate PK's would be essential, or using parent as a
grouping for many similar relations with a common index structure, where
duplicate PK in parent would be a disauster...)
As long a these methods are controlable and understood, then they can be
used.
One thing I would like is for the UNIQUE() to be respected with
inheritence. Therefore:
CREATE TABLE parent (
a INTEGER PRIMARY KEY,
UNIQUE (a)
)
Therefore giving some control... But there may be better methods :)
Ben
Jake Stride wrote:
> On Wed, 2003-07-09 at 17:56, 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.
>>
>>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.
>>
>
>
> We use inheritence alonside OO techniques and it is very useful. The
> duplicate keys is (I beleive) because of the way the relationship is
> inherited using directed graphs.
>
> The way that we have overcome the problem is so:
>
> create table a (id bigserial, ...., primary key(id));
>
> create table b (id bigserial, ..., primary key(id)) inherits (a);
>
> This means that you will always have a unique key in the inherited table
> as it 'replaces' the original id.
>
> Does this answer the question or am I off the mark?
>
> Jake
>
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? 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. > 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... 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! > 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. Certainly. But there are places where an OO methodology does have magor advantages in Relational Theory, just as it does in C / C++ coding. Abstraction and Polymophism being the biggest I can see from postings on this subject. Both of which can be difficult in traditional Relation Theory... But I look forward to some comments on this subject :) > 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'. Surelly the idea of inheritence is that parents have access to child data, not the other way round ?? Ben
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