Re: Auto creation of Partitions

Martijn van Oosterhout wrote:
> 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.

This sounds like what is really needed is a way to lock a certain
condition, namely the existance or non-existance of a record with
certain values in certain fields. This would not only help this case,
it would also help RI triggers, because those wouldn't have to acquire
a share lock on the referenced rows anymore.

As you pointed out, this would also make unique GiST indices possible

No real idea how to do this, though :-(

greetings, Florian Pfluge