Re: Auto creation of Partitions

On Wed, Mar 07, 2007 at 02:43:39AM +1030, Shane Ambler wrote:
> I think a way can be devised to maintain the primary key and unique
> constraints.
> If a search is done on the parent table, the planner knows to rewrite
> the query as a union (or union all) of all child tables that relate to
> the where clause, or all child tables if the where clause is not on the
> column/s used to partition, then this concept should be able to be
> converted to indexes as well, so that when a primary or unique index
> from a child table is inserted to, then each of the related child
> indexes is consulted to ensure uniqueness.

But that's where it breaks down: you not only need to check that the
row you're inserting is unique, you need to make sure that other people
trying to insert the same value see it.

After all, a unique index does contain multiple instances of any
particular value, it's just that the DB has verified that none of these
are visible to the same transaction. If there is a collision a lock is
taken that waits until the colliding insert commits or rejects.

With b-tree it's easy, every value can only appear in one place, so
it's a very simple lock. The reason why GiST doesn't support unique
indexes is due to the fact that any particular value could appear in
many places, so to stop concurrent inserts you need to lock *every*
page in the tree that another backend might want to insert the record.

I fear this will also be a problem for indexes over multiple tables, you
will need to lock the page in every index that some other DB might want
to insert the value into. All while avoiding deadlocks.

I think the problems are related. If you can solve it for multiple
tables, you can solve it for GiST indexes also.

> This would only apply if the partitioning was not done by the primary or
> unique column.

That's the easy case, no problem there.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.