Re: Auto creation of Partitions

NikhilS wrote:
> Hi,
>
> On 3/6/07, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>
>> NikhilS wrote:
>> > iv) Based on the PRIMARY, UNIQUE, REFERENCES information specified,
>> > pass it on to the children tables.
>>
>> How will you maintain a primary key in such a table, considering that
>> indexes can't span multiple tables?
>
>
> We will not (I know its a hard thing to do :) ), the intention is to use
> this information from the parent and make it a property of the child table.
> This will avoid the step for the user having to manually specify CREATE
> INDEX and the likes on all the children tables one-by-one.

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.
This would only apply if the partitioning was not done by the primary or
unique column.

>> 1) Whether we should use triggers/rules for step number (iii) above.
>> > Maybe rules is the way to go.
>>
>> Since this would basically be a case of the updatable rules problem, you
>> should review those discussions in the past to check whether the issues
>> mentioned there don't interfere with that plan.
>
> The rules mentioned here will be to specify that all the
> inserts/updates/deletes should go into proper children tables instead of
> the
> parent. I do not see the updateable rules problem with regards to this, but
> will check out the archives for discussion on this related to partitioning.
>

I would think that a trigger would be a better choice as I see the need
(or at least the possibility) for more than just a rewrite. When a value
is inserted that is outside of a condition currently covered by an
existing child table then a new child will need to be spawned to contain
the new data.

So say we partition by year and month of a date column? As new dates are
added new child tables would be created each month. Or is this beyond
the current plan and left to manual creation?

Will ALTER TABLE be extended to handle partitions? This will allow
partitioning existing tables (maybe without blocking access?) and allow
things like ALTER TABLE mytable ADD PARTITION (mydate within 200703)
and ALTER TABLE mytable DROP PARTITION (mydate within 199912) or would
dropping be covered by DELETE FROM mytable where mydate <= 199912 ?

Could such a syntax be devised for date columns? (month of mydate) or
similar to auto create partitions based on the year and month of a date
column? or will we just do CHECK(mydatecol >= 1/3/07 and mydatecol <=
31/3/07) for each month of data? Also (day of mydatecol) to partition
based on year and day of year.

Another syntax possibility - range(myserialcol of 500000) where new
child tables are created every 500000 rows?

Maybe I'm looking at auto-maintenance which is beyond any current planning?

--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz