From: David Fetter <david@fetter.org>
To: NikhilS <nikkhils@gmail.com>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paesold <mpaesold@gmx.at>
Cc: Jonah H. Harris <jonah.harris@gmail.com>
Cc: PostgreSQL Docs <pgsql-docs@postgresql.org>
Cc: PostgreSQL Patches <pgsql-patches@postgresql.org>
Subject: Re: [PATCHES] Partition: use triggers instead of rules
Date: Fri, 30 Nov 2007 20:56:20 -0800
Message-ID: <20071201045620.GW27157@fetter.org> (raw)
In-Reply-To: <d3c4af540711292304k7092335ete0470fd896bbb2ab@mail.gmail.com>
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>
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/donateAttachments:partition.diff (text/plain, 8.4K) download
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 >= 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,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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: david@fetter.org, nikkhils@gmail.com, stark@enterprisedb.com, jd@commandprompt.com, tgl@sss.pgh.pa.us, mpaesold@gmx.at, jonah.harris@gmail.com, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Partition: use triggers instead of rules
In-Reply-To: <20071201045620.GW27157@fetter.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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