Обсуждение: Inheritance, Primary Keys and Foreign Keys
Hi,I'm developing an object persistency framework for which I'd love to have better support for inheritance in PostgreSQL. I could already map subclasses with the current inheritance facilities, but the problem is with Primary and Foreign Keys. There's a TODO for implementing Indexes that hold information contained in different tables, but that seems to be difficult because of the need to create a new index structure. The new structure shouldn't be used by tables that don't have inherited tables because the new structure would hold a pointer to the appropiate table per entry and thus redundant in these cases. Even more, I've seen pointed by Tom Lane in a previous thread, that this would cause lock problems where a lock in a table is needed, as locking a table means locking its indexes. In my particular case (don't know about the SQL standard or other cases), it'd be enough if when an inherited table is created:- A primary key in the inherited table is created with the same columnsas the super table.- A trigger is created in the new table that ensures that this primary key doesn't exist in the super table.- A trigger is created in the super table that ensures that this primary key doesn't exist in it's sub tables. As I'm not an expert at all, I don't know if these would cause some side effects or if it's a good enough solution for the general problem. I don't know how multiple inheritance of tables with primary keys should be held (maybe all super tables should have the same primary key). For foreign keys, it seems as if simply selecting FROM a table instead ofthe current FROM ONLY would have the expected (byme :) behaviour. I'm very interested in improving inheritance support in PostgreSQL, and I'm willing to learn the current design and implementation in order to do it myself, or help wherever possible. So I'd like to know your ideas or problems you may find with this solution (if it's a solution at all :) Thanks in advance!
On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote: > In my particular case (don't know about the SQL standard or other cases), > it'd be enough if when an inherited table is created: > - A primary key in the inherited table is created with the same columns as > the super table. > - A trigger is created in the new table that ensures that this primary key > doesn't exist in the super table. > - A trigger is created in the super table that ensures that this primary key > doesn't exist in it's sub tables. Why not add these in your design rather than into the database? All of the above can be added using existing DDL and you can group things together in a transaction and call when required. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Of course, that's an option for my case. Just wanted to know if this solution could be useful for PostgreSQL in general. Mainly because I'll add some triggers to check what maybe PostgreSQL should do itself but it's unimplemented. If that's not interesting or a proper solution for PostgreSQL I'll add it using the existing DDL in my application and that's all. What do you think? A Tuesday 09 May 2006 21:43, Simon Riggs va escriure: > On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote: > > In my particular case (don't know about the SQL standard or other > > cases), it'd be enough if when an inherited table is created: > > - A primary key in the inherited table is created with the same columns > > as the super table. > > - A trigger is created in the new table that ensures that this primary > > key doesn't exist in the super table. > > - A trigger is created in the super table that ensures that this primary > > key doesn't exist in it's sub tables. > > Why not add these in your design rather than into the database? > > All of the above can be added using existing DDL and you can group > things together in a transaction and call when required.
Albert Cervera Areny wrote: > Of course, that's an option for my case. Just wanted to know if this solution > could be useful for PostgreSQL in general. Mainly because I'll add some > triggers to check what maybe PostgreSQL should do itself but it's > unimplemented. > > If that's not interesting or a proper solution for PostgreSQL I'll add it > using the existing DDL in my application and that's all. > > What do you think? > I think that if you want the database to improve its current inheritance behavior, then this trigger set is too limited. You need triggers that maintain both unique and primary keys and triggers that maintain cascade behavior. In order to make it really good, you would also need to add some functionality to the mechanisms that maintain references. Today, they don't recognize inheritance at all. Personally, I use Hibernate. It tries to compensate for the lack of these features but since it is a middle-tier (or client) solution, it's not ideal. Another client can still violate the rules and to maintain integrity in the client is negative from a performance standpoint. I think it would be great if PostgreSQL could provide a more complete set of features that would enable inheritance. A good start would be to extend it with the functionality needed to maintain references, cascade actions, and enforce unique constraints. On the other hand, inheritance is a tricky business and a good OO-RDB mapper will give you several choices of how it should be mapped. There's no "one size fits all". The best solution is probably if someone (you perhaps?) writes an external OO-RDB mapper module that executes in the backend. The author of such a tool would of course need some new nifty backend API's in order to do whats needed with references etc. I actually wrote something similar using Oracle a couple of years ago. It was based on type inheritance and views rather then tables and used 'instead of' actions on all views (Oracles own mechanisms where far to limited). In some respect, I think that is a better solution. Inheritance and all that comes with it is more a 'type' thing then a 'table' thing in my world. A view is then used to _map_ the types to persistent storage, i.e. the 'tables'. Regards, Thomas Hallgren
Of course, that's an option for my case. Just wanted to know if this solution could be useful for PostgreSQL in general. Mainly because I'll add some triggers to check what maybe PostgreSQL should do itself but it's unimplemented. If that's not interesting or a proper solution for PostgreSQL I'll add it using the existing DDL in my application and that's all. What do you think? A Tuesday 09 May 2006 21:43, Simon Riggs va escriure: > On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote: > > In my particular case (don't know about the SQL standard or other > > cases), it'd be enough if when an inherited table is created: > > - A primary key in the inherited table is created with the same columns > > as the super table. > > - A trigger is created in the new table that ensures that this primary > > key doesn't exist in the super table. > > - A trigger is created in the super table that ensures that this primary > > key doesn't exist in it's sub tables. > > Why not add these in your design rather than into the database? > > All of the above can be added using existing DDL and you can group > things together in a transaction and call when required.
A Saturday 13 May 2006 08:33, Thomas Hallgren va escriure: > Albert Cervera Areny wrote: > > Of course, that's an option for my case. Just wanted to know if this > > solution could be useful for PostgreSQL in general. Mainly because I'll > > add some triggers to check what maybe PostgreSQL should do itself but > > it's unimplemented. > > > > If that's not interesting or a proper solution for PostgreSQL I'll add it > > using the existing DDL in my application and that's all. > > > > What do you think? > > I think that if you want the database to improve its current inheritance > behavior, then this trigger set is too limited. You need triggers that > maintain both unique and primary keys and triggers that maintain cascade > behavior. True. I think those triggers should be used for all unique indexes, not only primary keys. What do you mean with triggers that maintain cascade behavior? > > In order to make it really good, you would also need to add some > functionality to the mechanisms that maintain references. Today, they don't > recognize inheritance at all. Indeed, foreign keys should be inherited, as well as unique keys. And to look for the reference they should SELECT FROM instead of SELECT FROM ONLY. > > Personally, I use Hibernate. It tries to compensate for the lack of these > features but since it is a middle-tier (or client) solution, it's not > ideal. Another client can still violate the rules and to maintain integrity > in the client is negative from a performance standpoint. I think it would > be great if PostgreSQL could provide a more complete set of features that > would enable inheritance. A good start would be to extend it with the > functionality needed to maintain references, cascade actions, and enforce > unique constraints. > > On the other hand, inheritance is a tricky business and a good OO-RDB > mapper will give you several choices of how it should be mapped. There's no > "one size fits all". The best solution is probably if someone (you > perhaps?) writes an external OO-RDB mapper module that executes in the > backend. The author of such a tool would of course need some new nifty > backend API's in order to do whats needed with references etc. > > I actually wrote something similar using Oracle a couple of years ago. It > was based on type inheritance and views rather then tables and used > 'instead of' actions on all views (Oracles own mechanisms where far to > limited). In some respect, I think that is a better solution. Inheritance > and all that comes with it is more a 'type' thing then a 'table' thing in > my world. A view is then used to _map_ the types to persistent storage, > i.e. the 'tables'. The library I'm developing (http://kandau.berlios.de) aims for very easy object persistency, and it offers a default O-R mapping schema. If the user wants, she can write her own, but as I'm working with PostgreSQL, I wanted to use the inheritance mechanism and extend it to fit the needs of this application. I think that inheritance at the database level as it's implemented in PostgreSQL is a very smart solution and I'd like it to be the default for my application. > > Regards, > Thomas Hallgren Thanks for your comments
Albert Cervera Areny wrote:> ...What do you mean with triggers that maintain cascade behavior? > It ties on to how references are handled. Since they currently ignore the inheritance aspect, you need triggers that enforce 'on cascade delete/update'. They will become obsolete if that changes (i.e. SELECT FROM instead of SELECT FROM ONLY). Regards, Thomas Hallgren
Ühel kenal päeval, N, 2006-05-11 kell 23:28, kirjutas Albert Cervera Areny: > Of course, that's an option for my case. Just wanted to know if this solution > could be useful for PostgreSQL in general. Mainly because I'll add some > triggers to check what maybe PostgreSQL should do itself but it's > unimplemented. > > If that's not interesting or a proper solution for PostgreSQL I'll add it > using the existing DDL in my application and that's all. > > What do you think? > > A Tuesday 09 May 2006 21:43, Simon Riggs va escriure: > > On Tue, 2006-05-09 at 01:20 +0200, Albert Cervera Areny wrote: > > > In my particular case (don't know about the SQL standard or other > > > cases), it'd be enough if when an inherited table is created: > > > - A primary key in the inherited table is created with the same columns > > > as the super table. This is the simple part. > > > - A trigger is created in the new table that ensures that this primary > > > key doesn't exist in the super table. This grows very expensive fast, once you have more thean one inherited table > > > - A trigger is created in the super table that ensures that this primary > > > key doesn't exist in it's sub tables. Ditto -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com NOTICE: This communication contains privileged or other confidential information. If you have received it in error, please advise the sender by reply email and immediately delete the message and any attachments without copying or disclosing the contents.