Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1 - Mailing list pgsql-hackers

From Nikhil Sontakke
Subject Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Date
Msg-id a301bfd90810310750pf108c69x36499546f406650f@mail.gmail.com
Whole thread Raw
In response to Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1  ("Nikhil Sontakke" <nikhil.sontakke@enterprisedb.com>)
Responses Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: David Fetter
Date:
Subject: Re: pre-MED
Next
From: "Kevin Grittner"
Date:
Subject: Re: Distinct types