On pią, 2007-01-05 at 10:55 +0100, Martijn van Oosterhout wrote: > On Fri, Jan 05, 2007 at 09:27:31AM +0100, Grzegorz Nowakowski wrote: > > Well, I never used partitioning and I don't know what it's worth but > > just after sending my original mail I got another variant of the idea: > > to duplicate columns (parent(p), child(p,c)), so inserts into child > > update both parent's and child's index. This way we trade space (common > > columns are replicated along inheritance hierarchy) and some speed > > (inserts into child are slower because they also have to update parent) > > for some other speed (selects work without join penalty). Yet still we > > have the primary benefit: parent's constraints and indexes work as > > expected. > > One of the reasons it hasn't happened yet is related to locking of > indexes. It is currently assumed that if you lock a table, you've > locked all the indexes implicitly. If you have an index that can be > updated by multiple tables, what are the locking semantics then? If you > want to drop the parent index, do you have to lock every child table? <disclaimer>My SQL experience isn't wide and broad. I just happen to use it and when programming queries I often think no in DB-like terms but instead as if I'm manipulating shadows of data used by application. I know it can cause problems and misunderstandings.</> Frankly, my answer would be 'yes, if you lock parent, you have to lock every child'. Only this way it makes sense: parent contains all children polymorphed into its base type so if I lock whole the stuff I'm locking every child's instance, tough luck. On the other hand that is behavior I would expect. If I want the primary key to be unique at the parent level, I want the Postgres to enforce it on every descendant, so I must to take it into account that additional lookups/locking on child tables would be performed. Best regards. -- Grzegorz Nowakowski
