Hi Nikhil,
Here are a couple of questions:
- How do you ALTER the table to repartition it?
- The trigger function for inserts could be improved by using ELSE
instead of independent IFs. This would ensure that the row is inserted
in at most 1 partition. The last ELSE should raise an exception if there
was no match (that would solve point 2 of your TODO list).
- Another option is to have a separate trigger per child table and chain
them to the master table. For example something like:
CREATE OR REPLACE FUNCTION child_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.date >= DATE(TG_ARGV[1]) AND NEW.date < DATE(TG_ARGV[2]) ) THEN
INSERT INTO TG_ARGV[0] VALUES (NEW.*);
RETURN NULL;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS insert_child_trigger ON master;
CREATE TRIGGER insert_child_trigger_y2008m01
BEFORE INSERT ON master
FOR EACH ROW EXECUTE PROCEDURE
child_insert_trigger_date(child_y2008m01, '2008-01-01', '2008-02-01');
CREATE TRIGGER insert_child_trigger_y2008m02
BEFORE INSERT ON master
FOR EACH ROW EXECUTE PROCEDURE
child_insert_trigger_date(child_y2008m02, '2008-02-01', '2008-03-01');
CREATE TRIGGER insert_child_trigger_y2008m03
BEFORE INSERT ON master
FOR EACH ROW EXECUTE PROCEDURE
child_insert_trigger_date(child_y2008m03, '2008-03-01', '2008-04-01');
This might make it easier when you want to alter a specific partition
rather than rewriting the whole trigger. Performance-wise, I am not sure
how chained triggers will compare to the big if/then/else trigger.
- In the case of an insert, could it be possible to avoid the cost of a
new INSERT statement (parser, planner, executor, etc...) by moving
directly the tuple in the right table like the COPY code does? If we had
an INSERT trigger code in C, given a HeapTuple and a target Relation we
should be able to call heap_insert_tuple directly, with no parsing,
planning, etc. required.
Thanks for your time,
Emmanuel
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
------------------------------------------------------------------------
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: [EMAIL PROTECTED]
Skype: emmanuel_cecchet
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
