Skip site navigation (1) Skip section navigation (2)

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

From: "Nikhil Sontakke" <nikhil(dot)sontakke(at)enterprisedb(dot)com>
To: "Jaime Casanova" <jcasanov(at)systemguards(dot)com(dot)ec>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Date: 2008-10-31 14:50:46
Message-ID: a301bfd90810310750pf108c69x36499546f406650f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

Attachment: partitioning-nov-commitfest-wip-v1.0.patch.tar.gz
Description: application/x-gzip (8.6 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2008-10-31 15:01:05
Subject: Re: Distinct types
Previous:From: David FetterDate: 2008-10-31 14:48:55
Subject: Re: pre-MED

pgsql-patches by date

Next:From: Emmanuel CecchetDate: 2008-10-31 18:42:00
Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Previous:From: Teodor SigaevDate: 2008-10-31 13:27:22
Subject: Re: [PATCHES] GIN improvements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group