Re: Auto creation of Partitions - Mailing list pgsql-hackers

On Mar 6, 2007, at 9:13 AM, Shane Ambler wrote:
> NikhilS wrote:
>> On 3/6/07, Peter Eisentraut <peter_e@gmx.net> wrote:
>>> 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.

There's no reason a new child has to be spawned, and I don't think  
that having a DML statement 'automagically' generating DDL is such a  
hot idea, either.

Also, there's nothing inherently wrong with having an 'overflow  
partition' (to use Oracle syntax) that holds values that don't fall  
in the range of any other tables. The obvious place to do that with  
our partitioning is in the parent table.

There are 2 other reasons to favor triggers though:

1) People (Josh Drake comes to mind) have found that if you get over  
a tiny number of partitions, the performance of rules is abysmal.

2) I believe it should be possible to construct an update trigger  
that allows you to perform updates that will place the row in  
question into a new partition. While I can see cases for simply  
disallowing updates to the partitioning key, I think there are also  
times when being able to do that would be very useful.

> 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 ?

I think it'd be great to make adding and removing partitions as  
simple as ALTER TABLE. I don't think that DELETE should be the  
mechanism to drop a partition, though. Again, DML statements  
shouldn't be performing DDL.

> 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?

I don't think it's worth it to burden the database with auto-creating  
time partitions; it's easy enough to setup a cron job to handle it.

It might be more useful to have the database handle things like  
partitioning on a SERIAL column, though I agree with Nikhils that  
this should wait.

Does any other database support 'automagic' partition creation? I  
know Oracle 9i doesn't... not sure about 10g or DB2 or MSSQL...
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)