Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CEBCC2E30DC; Wed, 28 Nov 2007 16:46:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 48852-05; Wed, 28 Nov 2007 16:46:43 -0400 (AST) Received: from fetter.org (start.fetter.org [66.92.188.65]) by postgresql.org (Postfix) with ESMTP id 7B3EC2E30C6; Wed, 28 Nov 2007 16:46:46 -0400 (AST) Received: by fetter.org (Postfix, from userid 500) id 8FAFC865E78; Wed, 28 Nov 2007 12:46:45 -0800 (PST) Date: Wed, 28 Nov 2007 12:46:45 -0800 From: David Fetter To: PostgreSQL Docs Cc: PostgreSQL Patches Subject: Re: Partition: use triggers instead of rules Message-ID: <20071128204644.GJ24146@fetter.org> References: <20071128202615.GG24146@fetter.org> <20071128123904.672beb8b@commandprompt.com> <20071128204120.GI24146@fetter.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="FsscpQKzF/jJk6ya" Content-Disposition: inline In-Reply-To: <20071128204120.GI24146@fetter.org> User-Agent: Mutt/1.5.17 (2007-11-01) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/107 X-Sequence-Number: 4640 --FsscpQKzF/jJk6ya Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate --FsscpQKzF/jJk6ya Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="partition.diff" 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 **** 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: ! ! ! 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 ); 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: ! 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 ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 ---- 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: ! ! ! 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; ! $$; ! ! ! 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. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); 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: ! 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; ! $$; ! ! ! Note that the WHERE clause in each section ! of the trigger function exactly matches the ! CHECK constraint for its partition. *************** *** 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. - - Partitioning can also be arranged using a UNION ALL - view: - - - 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; - - - However, the need to - recreate the view adds an extra step to adding and dropping - individual partitions of the data set. - --- 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. --FsscpQKzF/jJk6ya--