On Thu, Nov 29, 2007 at 11:42:18AM -0500, Bruce Momjian wrote: > Joshua D. Drake wrote: > > Tom Lane wrote: > > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > >> Rules are extremely slow in comparisons and not anywhere near as > > >> flexible. As I said up post yesterday... they work well in the basic > > >> partitioning configuration but anything else they are extremely > > >> deficient. > > > > > > I think that the above claim is exceedingly narrow-minded. > > > > We are talking about partitioning. It is supposed to be narrow-minded. > > Sure, but look at all the confusion we have had just on this list about > it. We had better state why triggers should be used in place of rules > _for_ _partitioning_ or that confusion will continue.
Please find enclosed a patch with use cases for each. 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 29 Nov 2007 17:51:46 -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,2623 ---- 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> ! <para> ! In the much rarer case where you only plan to do bulk ! inserts--batch inserts of logs would be an example--you can use ! RULEs instead of TRIGGERs. Here is an example of a RULE you ! would change via a cron job at the end of each month: <programlisting> ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2008m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); </programlisting> + </para> </sect2> <sect2 id="ddl-partitioning-managing-partitions"> *************** *** 2751,2772 **** <para> There is currently no way to verify that all of the <literal>CHECK</literal> constraints are mutually ! exclusive. Care is required by the database designer. </para> </listitem> - <listitem> - <para> - There is currently no simple way to specify that rows must not be - inserted into the master table. A <literal>CHECK (false)</literal> - constraint on the master table would be inherited by all child - tables, so that cannot be used for this purpose. One possibility is - to set up an <literal>ON INSERT</> trigger on the master table that - always raises an error. (Alternatively, such a trigger could be - used to redirect the data into the proper child table, instead of - using a set of rules as suggested above.) - </para> - </listitem> </itemizedlist> </para> --- 2780,2791 ---- <para> There is currently no way to verify that all of the <literal>CHECK</literal> constraints are mutually ! exclusive. It is easier to create code which generates ! partitions and creates and/or modifies associated objects than ! to write each by hand. </para> </listitem> </itemizedlist> </para>
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings