Thread: Partitioning documentation example
Simon, I was looking at the new table partitioning documentation that
recommends triggers:
http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION
and came upon this trigger function example:
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
...
ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
It seems to me it would be much clearer if we added a second example
that used to_char() to create the INSERT statement dynamically based on
NEW.logdate:
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'YYYY_mMM') || ' VALUES (NEW.*)';
END;
$$
LANGUAGE plpgsql;
It will of course fail if the table does not exist, which I think is
what we want. This trigger function would not have to be modified when
new tables are added.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Fri, 2008-01-04 at 10:27 -0500, Bruce Momjian wrote: > It seems to me it would be much clearer if we added a second example > that used to_char() to create the INSERT statement dynamically based on > NEW.logdate: > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETURNS TRIGGER AS $$ > BEGIN > EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'YYYY_mMM') || ' VALUES (NEW.*)'; > END; > $$ > LANGUAGE plpgsql; > > It will of course fail if the table does not exist, which I think is > what we want. This trigger function would not have to be modified when > new tables are added. I like your new trigger better than the old, but I am still uncomfortable with recommending we use triggers with COPY for performance reasons and we should add a caveat. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs wrote: > On Fri, 2008-01-04 at 10:27 -0500, Bruce Momjian wrote: > > > It seems to me it would be much clearer if we added a second example > > that used to_char() to create the INSERT statement dynamically based on > > NEW.logdate: > > > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > > RETURNS TRIGGER AS $$ > > BEGIN > > EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'YYYY_mMM') || ' VALUES (NEW.*)'; > > END; > > $$ > > LANGUAGE plpgsql; > > > > It will of course fail if the table does not exist, which I think is > > what we want. This trigger function would not have to be modified when > > new tables are added. > > I like your new trigger better than the old, but I am still > uncomfortable with recommending we use triggers with COPY for > performance reasons and we should add a caveat. OK, I will add this example in addition to the examples already present. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'YYYY_mMM') || ' VALUES (NEW.*)';
That won't actually work. Even if it did, I don't think we should be
recommending use of EXECUTE here; the performance implications are bad.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'YYYY_mMM') || ' VALUES (NEW.*)'; > > That won't actually work. Even if it did, I don't think we should be > recommending use of EXECUTE here; the performance implications are bad. Bummer. :-( -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
bruce wrote: > Simon, I was looking at the new table partitioning documentation that > recommends triggers: > > http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION > > and came upon this trigger function example: > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETURNS TRIGGER AS $$ > BEGIN > IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN > INSERT INTO measurement_y2006m02 VALUES (NEW.*); > ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN > INSERT INTO measurement_y2006m03 VALUES (NEW.*); > ... > ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN > INSERT INTO measurement_y2008m01 VALUES (NEW.*); > ELSE > RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; > END IF; > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; Because my EXECUTE example didn't work I have created a new example using date_trunc(), which I think is less error-prone than the comparisons done in the original example: CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF date_trunc('month', NEW.logdate) = '2006-02-01' THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF date_trunc('month', NEW.logdate) = '2006-03-01' THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ... ELSIF date_trunc('month', NEW.logdate) = '2008-01-01' THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Because my EXECUTE example didn't work I have created a new example
> using date_trunc(), which I think is less error-prone than the
> comparisons done in the original example:
This is not an improvement either. You can't represent the check
constraints that way (at least not if you want the planner to do
constraint exclusion with them) and I don't think it's "less
error-prone" to have a different representation in the trigger than
you have in the constraints.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Because my EXECUTE example didn't work I have created a new example > > using date_trunc(), which I think is less error-prone than the > > comparisons done in the original example: > > This is not an improvement either. You can't represent the check > constraints that way (at least not if you want the planner to do > constraint exclusion with them) and I don't think it's "less > error-prone" to have a different representation in the trigger than > you have in the constraints. I see, so date_trunc() can't be used for constraint exclusion in the CHECK constraint, and the trigger should match; makes sense. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Fri, Jan 04, 2008 at 12:25:59PM -0500, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'YYYY_mMM') || ' VALUES (NEW.*)'; > > That won't actually work. Even if it did, I don't think we should be > recommending use of EXECUTE here; the performance implications are bad. I think it's still worthy as an example. Not all partitioning systems need a high insert rate. And even if it does, that tends to be CPU-work; if you're partitioning you're likely IO-bound and not CPU-bound. Has anyone measured the performance difference? -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Attachment
Decibel! <decibel@decibel.org> writes:
> On Fri, Jan 04, 2008 at 12:25:59PM -0500, Tom Lane wrote:
>> That won't actually work.
> I think it's still worthy as an example.
I think what we have here is a failure to communicate. How are you
going to do performance measurement on something that does not work?
regards, tom lane