Re: Auto creation of Partitions

Hi,

Maybe, the dummy entry could be extended to contain the bounds (max/min) for
each of the other involved partitions and they could be updated each time a
DML happens across the partitions. That ways, an update to a particular
partition needs to lock out the others, examine the dummy entries in its own
index and follow it up with dummy entries update into other partitions if
the need be.

Ofcourse as you have mentioned all of this so needs to be done after a
careful think on the locking/deadlocking etc issues.

Regards,
Nikhils

On 3/7/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> I am wondering if we can implement unique indexes across several tables
> (inheritance hierarchy) not by using a single, big index covering all
> the tables, but rather by inserting a dummy entry into each partition's
> unique index. This dummy entry would have an expanded CTID which would
> include the tableoid, so it's possible to check it (albeit there is a
> problem in that we may require the opening of another heap to do the
> actual checking). These dummy entries could be removed by bulkcleanup
> as soon as the inserting transaction is no longer running, to avoid
> bloating the index too much. All said dummy index entries would be
> located at either the rightmost or the leftmost leaf, or close to it, so
> another idea is to have future inserters reuse the entry for a different
> key.
>
> The obvious problem with this is, naturally, the excess I/O that extra
> index traversing causes. The not so obvious ones are locking,
> deadlocking and the opening of other heaps and indexes while you do the
> insertion, which may be too expensive. On the other hand, maybe this
> idea is easier to implement than full-fledged cross-table indexes, so we
> could have richer partitioning earlier than when somebody finally bites
> the bullet and implements cross-table indexes.
>
> Or maybe this is just a dumb idea, but I had to let it out anyway :-)
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>

--
EnterpriseDB http://www.enterprisedb.com