Re: Table inheritance implementation.

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