Re: Auto creation of Partitions

>-----Original Message-----
>From: pgsql-hackers-owner@postgresql.org
>[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Alvaro Herrera
>Sent: woensdag 7 maart 2007 15:59
>To: NikhilS
>Cc: Zeugswetter Andreas ADI SD; Peter Eisentraut;
>pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] Auto creation of Partitions
>
>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.

I think that one big index is much better in this case. You are already
replicating the data and need a lot of work when updating the partition
structure (e.g. removing one).

[snip]

Rather:
If we have the partition relations (aka partition), then we can define a
unique index on it. This guarentees that there is at most one tuple with
the same value (for the specified columns) for every individual
partition.

Now for the inserts.
We already guarentee that is unique within the partition it lives. So we
must insert it first.
Next its to check the other partitions (in order!) for existence of a
row with a similar context. Of course we require full visibility of the
data.
We do the insert first, as this ensures other will find it. The order is
also important, otherwise there can be situations where we can't
guarentee the constraint.

Updates are exactly the same.
Deletes are trivial.

Perhaps you can do it without an index, but this is nothing different
than for a normal table.

- Joris Dobbelsteen