Partitioning documentation example
Simon, I was looking at the new table partitioning documentation that
recommends triggers:
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: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. +
Import Notes
Reply to msg id not found: | Resolved by subject fallback
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
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