Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1

Hi,

> > > >
>> > > >> Thanks for taking a look. But if I am not mistaken Gavin and co.
>> are
>> > working
>> > > >> on a much exhaustive proposal. In light of that maybe this patch
>> might
>> > not
>> > > >> be needed in the first place?
>> > > >>
>> > > >> I will wait for discussion and a subsequent collective consensus
>> here,
>> > > >> before deciding the further course of actions.
>> > > >
>> > > > I think it is unwise to wait on Gavin for a more complex
>> implemention
>> > > > --- we might end up with nothing for 8.4. As long as your syntax
>> is
>> > > > compatible with whatever Gavin proposed Gavin can add on to your
>> patch
>> > > > once it is applied.
>> > > >
>> > >
>> > > seems like you're a prophet... or i miss something?
>> > >
>> >
>> > :)
>> >
>> > Maybe I will try to summarize the functionality of this patch, rebase it
>> > against latest CVS head and try to get it on the commitfest queue
>> atleast
>> > for further feedback to keep the ball rolling on auto-partitioning...
>> >
>>
>> yeah! i was thinking on doing that but still have no time... and
>> frankly you're the best man for the job ;)
>>
>> one thing i was thinking of is to use triggers instead of rules just
>> as our current docs recommends
>> http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html
>>
>> with the benefit that a trigger can check if the child table exists
>> for the range being inserted and if not it can create it first...
>> haven't looked at the code in the detail but seems that your patch is
>> still missing the "create rule" part so we are in time to change
>> that... no?
>>
>
> Yes triggers should be used instead of rules. Automatic generation of
> rules/triggers would be kind of hard and needs some looking into. Also there
> are issues like checking mutual exclusivity of the partition clauses
> specified too (I have been maintaining that the onus of ensuring sane
> partition ranges/clauses should rest with the users atleast initially..).
>
> I will take a stab at this again whenever I get some free cycles.
>

I have synced up and modified the patch against latest CVS sources. Am
attaching the latest WIP patch here.

Am restating that its a WIP patch, more so because we really need feedback
on this before trying to expend any energy trying to come up with a
commit-able patch.

As per me, the syntax introduced by this patch should be similar to what was
proposed by Gavin quite a while back and this patch essentially tries to
bring together a bunch of ddl that would otherwise have been performed
step-by-step in a manual fashion earlier. To summarize this patch provides a
one-shot mechanism to:

-- * create master table
-- * create several child tables that inherit from this master table
-- * add appropriate constraints to each of the child tables
-- * create a trigger function to redirect insert, updates, deletes to
-- appropriate child tables (plpgsql language)
-- * create the trigger using the trigger function

I have created a new file (src/test/regress/sql/partition.sql) to show a
couple of examples of the grammar and the working functionality:

There are TODOs like:
-- logic to ensure unique trigger function and trigger names
-- The trigger function body could raise an exception if the
insert/update/delete operation does not fit into any single partition
-- logic to check mutual exclusivity of ranges/lists
-- misc. issues to convert it from wip to commit-ready

If we think this is ok as a first step towards auto-partitioning then we can
do something more with this patch.

Regards,
Nikhils
--
http://www.enterprisedb.com