On Wed, Nov 28, 2007 at 12:41:20PM -0800, David Fetter wrote:
> On Wed, Nov 28, 2007 at 12:39:04PM -0800, Joshua D. Drake wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Wed, 28 Nov 2007 12:26:15 -0800
> > David Fetter <[EMAIL PROTECTED]> wrote:
> >
> > > Folks,
> > >
> > > Best practices for partitioning so far have shown that TRIGGERs are
> > > better than RULEs for most cases. Please find attached a patch which
> > > reflects this.
> > >
> > > Thanks to Robert Treat for help putting this together :)
> > >
> > > Cheers,
> > > David.
> >
> > +1
> >
> > Joshua D. Drake
>
> Per Robert, I've also dropped the UNION partitioning suggestion as
> it's pretty useless.
Oops. Patch including *both* changes attached this time.
Cheers,
David.
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Index: doc/src/sgml/ddl.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v
retrieving revision 1.77
diff -c -r1.77 ddl.sgml
*** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 -0000 1.77
--- doc/src/sgml/ddl.sgml 28 Nov 2007 20:44:57 -0000
***************
*** 2510,2564 ****
<listitem>
<para>
If data will be added only to the latest partition, we can
! set up a very simple rule to insert data. We must
! redefine this each month so that it always points to the
! current partition:
!
! <programlisting>
! CREATE OR REPLACE RULE measurement_current_partition AS
! ON INSERT TO measurement
! DO INSTEAD
! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
! NEW.logdate,
! NEW.peaktemp,
! NEW.unitsales );
</programlisting>
We might want to insert data and have the server automatically
locate the partition into which the row should be added. We
! could do this with a more complex set of rules as shown below:
<programlisting>
! CREATE RULE measurement_insert_y2004m02 AS
! ON INSERT TO measurement WHERE
! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
! DO INSTEAD
! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
! NEW.logdate,
! NEW.peaktemp,
! NEW.unitsales );
! ...
! CREATE RULE measurement_insert_y2005m12 AS
! ON INSERT TO measurement WHERE
! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
! DO INSTEAD
! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
! NEW.logdate,
! NEW.peaktemp,
! NEW.unitsales );
! CREATE RULE measurement_insert_y2006m01 AS
! ON INSERT TO measurement WHERE
! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
! DO INSTEAD
! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
! NEW.logdate,
! NEW.peaktemp,
! NEW.unitsales );
! </programlisting>
!
! Note that the <literal>WHERE</literal> clause in each rule
! exactly matches the <literal>CHECK</literal>
! constraint for its partition.
</para>
</listitem>
</orderedlist>
--- 2510,2589 ----
<listitem>
<para>
If data will be added only to the latest partition, we can
! set up a very simple trigger function to insert data. We must
! redefine this each month so that it always points to the current
! partition:
!
! <programlisting>
! CREATE OR REPLACE FUNCTION measurement_current_partition()
! RETURNS TRIGGER
! LANGUAGE plpgsql
! AS $$
! BEGIN
! INSERT INTO measurement_y2006m01
! VALUES (
! NEW.city_id,
! NEW.logdate,
! NEW.peaktemp,
! NEW.unitsales
! );
! RETURN NEW;
! END;
! $$;
! </programlisting>
!
! The first time we create the table, we create a trigger which
! calls the above trigger function. When we replace the trigger
! function, we don't need to replace the trigger.
!
! <programlisting>
! CREATE TRIGGER insert_measurement_current_partition
! BEFORE INSERT
! ON measurement
! EXECUTE PROCEDURE measurement_current_partition();
</programlisting>
We might want to insert data and have the server automatically
locate the partition into which the row should be added. We
! could do this with a more complex trigger function as shown
! below:
<programlisting>
! CREATE OR REPLACE FUNCTION measurement_insert()
! RETURNS TRIGGER
! LANGUAGE plpgsql
! AS $$
! BEGIN
! IF ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
THEN
!
! INSERT INTO measurement_y2004m02
! VALUES (
! NEW.city_id,
! NEW.logdate,
! NEW.peaktemp,
! NEW.unitsales
! );
! ELSIF ( logdate >= DATE '2005-12-01' AND logdate < DATE
'2006-01-01' ) THEN
! ...
! ELSIF ( logdate >= DATE '2008-01-01' AND logdate < DATE
'2006-02-01' ) THEN
! INSERT INTO measurement_y2008m01
! VALUES (
! NEW.city_id,
! NEW.logdate,
! NEW.peaktemp,
! NEW.unitsales
! );
! ELSE
! RAISE EXCEPTION 'In, measurement_insert(), the date out of range.
Fix the trigger function!';
! END IF;
! RETURN NULL;
! END;
! $$;
! </programlisting>
!
! Note that the <literal>WHERE</literal> clause in each section
! of the trigger function exactly matches the
! <literal>CHECK</literal> constraint for its partition.
</para>
</listitem>
</orderedlist>
***************
*** 2568,2594 ****
As we can see, a complex partitioning scheme could require a
substantial amount of DDL. In the above example we would be
creating a new partition each month, so it might be wise to write a
! script that generates the required DDL automatically.
</para>
- <para>
- Partitioning can also be arranged using a <literal>UNION ALL</literal>
- view:
-
- <programlisting>
- CREATE VIEW measurement AS
- SELECT * FROM measurement_y2004m02
- UNION ALL SELECT * FROM measurement_y2004m03
- ...
- UNION ALL SELECT * FROM measurement_y2005m11
- UNION ALL SELECT * FROM measurement_y2005m12
- UNION ALL SELECT * FROM measurement_y2006m01;
- </programlisting>
-
- However, the need to
- recreate the view adds an extra step to adding and dropping
- individual partitions of the data set.
- </para>
</sect2>
<sect2 id="ddl-partitioning-managing-partitions">
--- 2593,2603 ----
As we can see, a complex partitioning scheme could require a
substantial amount of DDL. In the above example we would be
creating a new partition each month, so it might be wise to write a
! script that generates the required DDL automatically. You could
! also write a function to determine the partition dynamically
! before doing the INSERT.
</para>
</sect2>
<sect2 id="ddl-partitioning-managing-partitions">
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
