On Feb 13, 2007, at 10:29 AM, Pascal Damian wrote: > Hi all, > > Has anyone worked with Rose::DB::Object and PostgreSQL's table > inheritance? For example: > > CREATE TABLE party (id INT PRIMARY KEY, birthday DATE); > CREATE TABLE person INHERITS party (firstname TEXT, lastname TEXT); > CREATE TABLE organization INHERITS party (name TEXT); > > There are also a couple of triggers to make sure that id's are > unique across the whole hierarchy (because PostgreSQL currently > doesn't automatically enforce it).
FWIW, i dropped using table inheritance in postgres. the way it was internally handled wasn't fast enough - no matter how i keyed and indexed the tables or tried to influence the optimizer, the internal system always used a slow join i ended up doing this model : party ( id BIGSERIAL , type_id INT references party_type ) party_type (id , type ) ; # values (11, person ); (12,organization); party_person ( id refernces party(id) , etc ) party_organization ( id refernces party(id) , etc ) then use a trigger on making a party / person to use the serial from id, and insert the type as well -- just like a lookup table. i found it way more extensible and suited for the queries i needed to do and, well, a whole lot faster. your mileage may vary, but i'd suggest doing a quick explain on your inherited tables. the joins the pg optimizer did were just not acceptable to me. // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier. Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
