Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 408942E37BA;
	Sat,  1 Dec 2007 00:56:33 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 73627-08; Sat,  1 Dec 2007 00:56:22 -0400 (AST)
Received: from fetter.org (start.fetter.org [66.92.188.65])
	by postgresql.org (Postfix) with ESMTP id AE9212E37B9;
	Sat,  1 Dec 2007 00:56:22 -0400 (AST)
Received: by fetter.org (Postfix, from userid 500)
	id 08831FBC7BB; Fri, 30 Nov 2007 20:56:20 -0800 (PST)
Date: Fri, 30 Nov 2007 20:56:20 -0800
From: David Fetter <david@fetter.org>
To: NikhilS <nikkhils@gmail.com>
Cc: Gregory Stark <stark@enterprisedb.com>,
	"Joshua D. Drake" <jd@commandprompt.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, Michael Paesold <mpaesold@gmx.at>,
	"Jonah H. Harris" <jonah.harris@gmail.com>,
	PostgreSQL Docs <pgsql-docs@postgresql.org>,
	PostgreSQL Patches <pgsql-patches@postgresql.org>
Subject: Re: [PATCHES] Partition: use triggers instead of rules
Message-ID: <20071201045620.GW27157@fetter.org>
References: <474EDAE2.3010705@gmx.at> <474EE25E.3080304@commandprompt.com>
	<14455.1196353045@sss.pgh.pa.us>
	<474EE992.2050209@commandprompt.com>
	<87zlwxj7xu.fsf@oxford.xeocode.com>
	<20071129091359.206fd8f2@commandprompt.com>
	<87ve7lj6y8.fsf@oxford.xeocode.com>
	<20071129095233.67f2b913@commandprompt.com>
	<87d4tser8g.fsf@oxford.xeocode.com>
	<d3c4af540711292304k7092335ete0470fd896bbb2ab@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="eJnRUKwClWJh1Khz"
Content-Disposition: inline
In-Reply-To: <d3c4af540711292304k7092335ete0470fd896bbb2ab@mail.gmail.com>
User-Agent: Mutt/1.5.17 (2007-11-01)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/1
X-Sequence-Number: 4669


--eJnRUKwClWJh1Khz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, Nov 30, 2007 at 12:34:05PM +0530, NikhilS wrote:
> Hi,
> 
> Another reason to go along with triggers is that "COPY" honors
> triggers, but does not honor rules. While trying to do bulk inserts
> into a parent of partitioned tables where rules are being employed,
> the COPY operation will not be so straightforward.

Folks,

Does my latest patch attached address this well enough?

Cheers,
David.
-- 
David Fetter <david@fetter.org> 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

--eJnRUKwClWJh1Khz
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	1 Dec 2007 04:55: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 &gt;= DATE '2004-02-01' AND logdate &lt; 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 &gt;= DATE '2005-12-01' AND logdate &lt; 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 &gt;= DATE '2006-01-01' AND logdate &lt; 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 &gt;= DATE '2004-02-01' AND logdate &lt; DATE '2004-03-01' ) THEN
! 
!         INSERT INTO measurement_y2004m02
!         VALUES (
!             NEW.city_id,
!             NEW.logdate,
!             NEW.peaktemp,
!             NEW.unitsales
!         );
!     ELSIF ( logdate &gt;= DATE '2005-12-01' AND logdate &lt; DATE '2006-01-01' ) THEN
!     ...
!     ELSIF ( logdate &gt;= DATE '2008-01-01' AND logdate &lt; 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,2624 ----
       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, but not COPY, 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>
  
--- 2781,2792 ----
       <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>
  

--eJnRUKwClWJh1Khz--