pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning documentation example
10+ messages / 4 participants
[nested] [flat]
* Partitioning documentation example
@ 2008-01-04 15:27 Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 10+ messages in thread
From: Bruce Momjian @ 2008-01-04 15:27 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-docs
Simon, I was looking at the new table partitioning documentation that
recommends triggers:
http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATIO...
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. +
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-01-04 16:15 Simon Riggs <simon@2ndquadrant.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 10+ messages in thread
From: Simon Riggs @ 2008-01-04 16:15 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-01-04 16:54 Bruce Momjian <bruce@momjian.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2008-01-04 16:54 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-docs
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. +
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-01-04 17:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 2 replies; 10+ messages in thread
From: Tom Lane @ 2008-01-04 17:25 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-01-04 17:32 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2008-01-04 17:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-docs
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. +
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-01-05 17:37 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2008-01-05 17:37 UTC (permalink / raw)
To: pgsql-docs; +Cc: Simon Riggs <simon@2ndquadrant.com>
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-IMPLEMENTATIO...
>
> 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. +
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-01-05 18:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2008-01-05 18:08 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs; Simon Riggs <simon@2ndquadrant.com>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-01-05 19:01 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2008-01-05 19:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs; Simon Riggs <simon@2ndquadrant.com>
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. +
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-02-06 05:05 Decibel! <decibel@decibel.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Decibel! @ 2008-02-06 05:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Simon Riggs <simon@2ndquadrant.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning documentation example
@ 2008-02-06 05:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2008-02-06 05:35 UTC (permalink / raw)
To: Decibel! <decibel@decibel.org>; +Cc: Bruce Momjian <bruce@momjian.us>; Simon Riggs <simon@2ndquadrant.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-02-06 05:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-04 15:27 Partitioning documentation example Bruce Momjian <bruce@momjian.us>
2008-01-04 16:15 ` Simon Riggs <simon@2ndquadrant.com>
2008-01-04 16:54 ` Bruce Momjian <bruce@momjian.us>
2008-01-04 17:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-04 17:32 ` Bruce Momjian <bruce@momjian.us>
2008-02-06 05:05 ` Decibel! <decibel@decibel.org>
2008-02-06 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-05 17:37 Re: Partitioning documentation example Bruce Momjian <bruce@momjian.us>
2008-01-05 18:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-05 19:01 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox