pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPartition: use triggers instead of rules
38+ messages / 13 participants
[nested] [flat]
* Partition: use triggers instead of rules
@ 2007-11-28 20:26 David Fetter <david@fetter.org>
2007-11-28 20:39 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 38+ messages in thread
From: David Fetter @ 2007-11-28 20:26 UTC (permalink / raw)
To: pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
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.
--
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
Attachments:
partition.diff (text/plain, 5.8K) 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 28 Nov 2007 20:23:44 -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,2574 ****
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>
--- 2593,2601 ----
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>
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-11-28 20:39 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-28 20:41 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
1 sibling, 1 reply; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-28 20:39 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wed, 28 Nov 2007 12:26:15 -0800
David Fetter <david@fetter.org> 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
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHTdHoATb/zqfZUUQRAnpWAJ9xHqMnNorANuS9r8Hm90tn2bmZIwCglwlq
D7Q1C5vcbvQ/JiqVcv0wCho=
=NcWZ
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:39 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-28 20:41 ` David Fetter <david@fetter.org>
2007-11-28 20:46 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
0 siblings, 1 reply; 38+ messages in thread
From: David Fetter @ 2007-11-28 20:41 UTC (permalink / raw)
To: pgsql-docs; +Cc: PostgreSQL Patches <pgsql-patches@postgresql.org>
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 <david@fetter.org> 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.
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
Attachments:
partition.diff (text/plain, 1.1K) 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 28 Nov 2007 20:39:48 -0000
***************
*** 2571,2594 ****
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">
--- 2571,2576 ----
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:39 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-28 20:41 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-11-28 20:46 ` David Fetter <david@fetter.org>
0 siblings, 0 replies; 38+ messages in thread
From: David Fetter @ 2007-11-28 20:46 UTC (permalink / raw)
To: pgsql-docs; +Cc: PostgreSQL Patches <pgsql-patches@postgresql.org>
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 <david@fetter.org> 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 <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
Attachments:
partition.diff (text/plain, 6.5K) 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 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">
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-11-28 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 2 replies; 38+ messages in thread
From: Tom Lane @ 2007-11-28 20:53 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
David Fetter <david@fetter.org> writes:
> 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.
Entirely removing the example of how to do it with rules doesn't
seem like a good idea.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-28 20:57 ` David Fetter <david@fetter.org>
2007-11-28 21:03 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-28 22:17 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 38+ messages in thread
From: David Fetter @ 2007-11-28 20:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > 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.
>
> Entirely removing the example of how to do it with rules doesn't
> seem like a good idea.
It does to me. I haven't found a case yet where rules worked even as
well as triggers.
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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-11-28 21:03 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-28 21:03 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wed, 28 Nov 2007 12:57:10 -0800
David Fetter <david@fetter.org> wrote:
> On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:
> > David Fetter <david@fetter.org> writes:
> > > 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.
> >
> > Entirely removing the example of how to do it with rules doesn't
> > seem like a good idea.
>
> It does to me. I haven't found a case yet where rules worked even as
> well as triggers.
I have yet to see a place that rules are appropriate at all with
partitioning. The only half reasonable argument to their existence with
it is that they are easier for someone with an extremely simple
partitioning configuration.
However rules are so deficient on anything except the absolute most
basic example of partitioning that I think we are doing our new
partitioning users a disservice by stating anything about rules except
in passing.
Sincerely,
Joshua D. Drake
>
> Cheers,
> David.
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHTdeuATb/zqfZUUQRAvf3AJ96e4YynZ/rYv6egG+w+85Ms+ihogCgjIlh
6xWR0nzceoJYCiQl+ffFTK8=
=XREP
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-11-28 22:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 00:53 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
1 sibling, 1 reply; 38+ messages in thread
From: Tom Lane @ 2007-11-28 22:17 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
David Fetter <david@fetter.org> writes:
> On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:
>> Entirely removing the example of how to do it with rules doesn't
>> seem like a good idea.
> It does to me. I haven't found a case yet where rules worked even as
> well as triggers.
I don't have a problem with emphasizing triggers as the preferred
solution, but we should keep the older example, if only because people
are going to see DB schemas that use that approach, and they won't
understand what's going on (or realize they could convert) if they've
not seen an example.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 22:17 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-29 00:53 ` David Fetter <david@fetter.org>
2007-11-29 01:06 ` Re: Partition: use triggers instead of rules Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 38+ messages in thread
From: David Fetter @ 2007-11-29 00:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Wed, Nov 28, 2007 at 05:17:38PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote:
> >> Entirely removing the example of how to do it with rules doesn't
> >> seem like a good idea.
>
> > It does to me. I haven't found a case yet where rules worked even
> > as well as triggers.
>
> I don't have a problem with emphasizing triggers as the preferred
> solution, but we should keep the older example, if only because
> people are going to see DB schemas that use that approach, and they
> won't understand what's going on (or realize they could convert) if
> they've not seen an example.
Greg Sabino Mullane managed to contrive an example where RULEs might
conceivably be the least-bad way to do this, that being a machine
where no PLs may be installed.
Telling people how to do this is not *quite* as bad as describing how
to do EAV, but it's pretty close.
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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 22:17 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 00:53 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-11-29 01:06 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-29 01:07 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 06:23 ` Re: [DOCS] Partition: use triggers instead of rules Jeremy Drake <pgsql@jdrake.com>
0 siblings, 2 replies; 38+ messages in thread
From: Alvaro Herrera @ 2007-11-29 01:06 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
David Fetter wrote:
> Greg Sabino Mullane managed to contrive an example where RULEs might
> conceivably be the least-bad way to do this, that being a machine
> where no PLs may be installed.
Perhaps this just means we should consider installing plpgsql by
default.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"There was no reply" (Kernel Traffic)
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 22:17 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 00:53 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 01:06 ` Re: Partition: use triggers instead of rules Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2007-11-29 01:07 ` David Fetter <david@fetter.org>
1 sibling, 0 replies; 38+ messages in thread
From: David Fetter @ 2007-11-29 01:07 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Wed, Nov 28, 2007 at 10:06:01PM -0300, Alvaro Herrera wrote:
> David Fetter wrote:
>
> > Greg Sabino Mullane managed to contrive an example where RULEs
> > might conceivably be the least-bad way to do this, that being a
> > machine where no PLs may be installed.
>
> Perhaps this just means we should consider installing plpgsql by
> default.
I'm all for that :)
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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 22:17 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 00:53 ` Re: Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 01:06 ` Re: Partition: use triggers instead of rules Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2007-11-29 06:23 ` Jeremy Drake <pgsql@jdrake.com>
1 sibling, 0 replies; 38+ messages in thread
From: Jeremy Drake @ 2007-11-29 06:23 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: David Fetter <david@fetter.org>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Wed, 28 Nov 2007, Alvaro Herrera wrote:
> David Fetter wrote:
>
> > Greg Sabino Mullane managed to contrive an example where RULEs might
> > conceivably be the least-bad way to do this, that being a machine
> > where no PLs may be installed.
>
> Perhaps this just means we should consider installing plpgsql by
> default.
I have run into this myself, and a patch that I contributed (which made it
in to 8.3) made it possible for a database owner to create trusted
languages from templates in the default configuration. Which means that
if an admin wants to prevent usage of the language, they can revoke the
right to create it, but db owners still "opt-in" to any languages they
want.
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-29 02:58 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
1 sibling, 1 reply; 38+ messages in thread
From: Jonah H. Harris @ 2007-11-29 02:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Fetter <david@fetter.org>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Entirely removing the example of how to do it with rules doesn't
> seem like a good idea.
Agreed.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
@ 2007-11-29 03:17 ` David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 38+ messages in thread
From: David Fetter @ 2007-11-29 03:17 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:
> On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Entirely removing the example of how to do it with rules doesn't
> > seem like a good idea.
>
> Agreed.
Do you have an example of one use case where using RULEs rather than
TRIGGERs is a good idea?
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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-11-29 05:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:36 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
0 siblings, 2 replies; 38+ messages in thread
From: Tom Lane @ 2007-11-29 05:55 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
David Fetter <david@fetter.org> writes:
> On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:
>> On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Entirely removing the example of how to do it with rules doesn't
>>> seem like a good idea.
>>
>> Agreed.
> Do you have an example of one use case where using RULEs rather than
> TRIGGERs is a good idea?
The argument I made for keeping the example around is not dependent on
the assumption that using a rule is a good idea. It's dependent on the
established fact that we have recommended that in prior releases, and
therefore people are going to be seeing that construct in real
databases.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-29 06:36 ` David Fetter <david@fetter.org>
1 sibling, 0 replies; 38+ messages in thread
From: David Fetter @ 2007-11-29 06:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Thu, Nov 29, 2007 at 12:55:53AM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:
> >> On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>> Entirely removing the example of how to do it with rules doesn't
> >>> seem like a good idea.
> >>
> >> Agreed.
>
> > Do you have an example of one use case where using RULEs rather
> > than TRIGGERs is a good idea?
>
> The argument I made for keeping the example around is not dependent
> on the assumption that using a rule is a good idea. It's dependent
> on the established fact that we have recommended that in prior
> releases, and therefore people are going to be seeing that construct
> in real databases.
We've corrected mistakes in the manual before :)
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
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-29 06:41 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
1 sibling, 1 reply; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-29 06:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thu, 29 Nov 2007 00:55:53 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> David Fetter <david@fetter.org> writes:
> > On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote:
> >> On Nov 28, 2007 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>> Entirely removing the example of how to do it with rules doesn't
> >>> seem like a good idea.
> >>
> >> Agreed.
>
> > Do you have an example of one use case where using RULEs rather than
> > TRIGGERs is a good idea?
>
> The argument I made for keeping the example around is not dependent on
> the assumption that using a rule is a good idea. It's dependent on
> the established fact that we have recommended that in prior releases,
> and therefore people are going to be seeing that construct in real
> databases.
And they could refer back to the older version of the documentation for
it. In fact, we should mention that in the patch:
<note><para>If you have an partitioning setup that uses rules please
refer to the 8.2 documentation on partitioning</para></note>
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 4: Have you searched our
> list archives?
>
> http://archives.postgresql.org
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHTl84ATb/zqfZUUQRAtscAJ9h77nDNJ3ZggWgocXDQaFE/S998gCcDtiV
wKH3BejsoL0fR5D3KHhKaEc=
=zpPv
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 15:05 ` NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
0 siblings, 1 reply; 38+ messages in thread
From: NikhilS @ 2007-11-29 15:05 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
>
> > The argument I made for keeping the example around is not dependent on
> > the assumption that using a rule is a good idea. It's dependent on
> > the established fact that we have recommended that in prior releases,
> > and therefore people are going to be seeing that construct in real
> > databases.
>
> And they could refer back to the older version of the documentation for
> it. In fact, we should mention that in the patch:
>
> <note><para>If you have a partitioning setup that uses rules please
> refer to the 8.2 documentation on partitioning</para></note>
>
+1
Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
@ 2007-11-29 15:29 ` Michael Paesold <mpaesold@gmx.at>
2007-11-29 15:48 ` Re: [DOCS] Partition: use triggers instead of rules Usama Dar <munir.usama@gmail.com>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
0 siblings, 2 replies; 38+ messages in thread
From: Michael Paesold @ 2007-11-29 15:29 UTC (permalink / raw)
To: NikhilS <nikkhils@gmail.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
NikhilS wrote:
>> The argument I made for keeping the example around is not
> dependent on
>> the assumption that using a rule is a good idea. It's dependent on
>> the established fact that we have recommended that in prior
>> releases, and therefore people are going to be seeing that
>> construct in real databases.
>
> And they could refer back to the older version of the documentation
> for it. In fact, we should mention that in the patch:
>
> <note><para>If you have a partitioning setup that uses rules please
> refer to the 8.2 documentation on partitioning</para></note>
>
> +1
I would also add another sentence about *why* the recommendation was
changed. We have one rule-based setup here, and it has been working
flawlessly for us,... so personally I don't even know the reasons.
Best Regards
Michael Paesold
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
@ 2007-11-29 15:48 ` Usama Dar <munir.usama@gmail.com>
1 sibling, 0 replies; 38+ messages in thread
From: Usama Dar @ 2007-11-29 15:48 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; +Cc: NikhilS <nikkhils@gmail.com>; Joshua D. Drake <jd@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
i would say have both, but just mention that for performance reasons its
better to use triggers.
On Nov 29, 2007 8:29 PM, Michael Paesold <mpaesold@gmx.at> wrote:
> NikhilS wrote:
> >> The argument I made for keeping the example around is not
> > dependent on
> >> the assumption that using a rule is a good idea. It's dependent on
> >> the established fact that we have recommended that in prior
> >> releases, and therefore people are going to be seeing that
> >> construct in real databases.
> >
> > And they could refer back to the older version of the documentation
> > for it. In fact, we should mention that in the patch:
> >
> > <note><para>If you have a partitioning setup that uses rules please
> > refer to the 8.2 documentation on partitioning</para></note>
> >
> > +1
>
> I would also add another sentence about *why* the recommendation was
> changed. We have one rule-based setup here, and it has been working
> flawlessly for us,... so personally I don't even know the reasons.
>
> Best Regards
> Michael Paesold
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Usama Munir Dar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
@ 2007-11-29 16:01 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:06 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-29 16:01 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; +Cc: NikhilS <nikkhils@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
Michael Paesold wrote:
> NikhilS wrote:
>> <note><para>If you have a partitioning setup that uses rules please
>> refer to the 8.2 documentation on partitioning</para></note>
>>
>> +1
>
> I would also add another sentence about *why* the recommendation was
> changed. We have one rule-based setup here, and it has been working
> flawlessly for us,... so personally I don't even know the reasons.
>
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.
Joshua D. Drake
> Best Regards
> Michael Paesold
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 16:06 ` Michael Paesold <mpaesold@gmx.at>
1 sibling, 0 replies; 38+ messages in thread
From: Michael Paesold @ 2007-11-29 16:06 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: NikhilS <nikkhils@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
Joshua D. Drake wrote:
> Michael Paesold wrote:
>> I would also add another sentence about *why* the recommendation was
>> changed. We have one rule-based setup here, and it has been working
>> flawlessly for us,... so personally I don't even know the reasons.
>>
>
> 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.
Ah, thanks for that summary. How do they compare to triggers dynamically
EXECUTEing the inserts? Is that a better solution, or should one really
just use the IF ... ELSIF ... ELSIF ... ELSE pattern as suggested in the
new docs? (Which means one has to re-create the complete trigger each
time a partition is added.)
Best Regards
Michael Paesold
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 38+ messages in thread
From: Tom Lane @ 2007-11-29 16:17 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
"Joshua D. Drake" <jd@commandprompt.com> 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. A trigger
will probably beat a rule for inserts/updates involving a small number
of rows. For large numbers of rows, like an INSERT/SELECT from another
large table, the rule is likely to win, because its overhead is paid
once per query not once per row. Also, if you implement the trigger
with an EXECUTE (forcing a planning cycle) intead of hard-coded
commands, the speed advantage becomes even more dubious.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-29 16:32 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:42 ` Re: [PATCHES] Partition: use triggers instead of rules Bruce Momjian <bruce@momjian.us>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:12 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 17:19 ` Re: [PATCHES] Partition: use triggers instead of rules Andrew Dunstan <andrew@dunslane.net>
0 siblings, 4 replies; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-29 16:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> 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.
> A trigger
> will probably beat a rule for inserts/updates involving a small number
> of rows.
Which is exactly what partitioning is doing.
For large numbers of rows, like an INSERT/SELECT from another
> large table, the rule is likely to win, because its overhead is paid
> once per query not once per row. Also, if you implement the trigger
> with an EXECUTE (forcing a planning cycle) intead of hard-coded
> commands, the speed advantage becomes even more dubious.
Not for partitioning. Although I agree with your sentiments for normal
operation.
Sincerely,
Joshua D. Drake
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 16:42 ` Bruce Momjian <bruce@momjian.us>
2007-11-29 16:49 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:52 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
3 siblings, 2 replies; 38+ messages in thread
From: Bruce Momjian @ 2007-11-29 16:42 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
Joshua D. Drake wrote:
> Tom Lane wrote:
> > "Joshua D. Drake" <jd@commandprompt.com> 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.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:42 ` Re: [PATCHES] Partition: use triggers instead of rules Bruce Momjian <bruce@momjian.us>
@ 2007-11-29 16:49 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-29 16:49 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
Bruce Momjian wrote:
> Joshua D. Drake wrote:
>> Tom Lane wrote:
>>> "Joshua D. Drake" <jd@commandprompt.com> 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.
Sure I have no problem with that.
Joshua D. Drake
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:42 ` Re: [PATCHES] Partition: use triggers instead of rules Bruce Momjian <bruce@momjian.us>
@ 2007-11-29 17:52 ` David Fetter <david@fetter.org>
1 sibling, 0 replies; 38+ messages in thread
From: David Fetter @ 2007-11-29 17:52 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Thu, Nov 29, 2007 at 11:42:18AM -0500, Bruce Momjian wrote:
> Joshua D. Drake wrote:
> > Tom Lane wrote:
> > > "Joshua D. Drake" <jd@commandprompt.com> 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 <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
Attachments:
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 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>
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 17:08 ` Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
3 siblings, 1 reply; 38+ messages in thread
From: Gregory Stark @ 2007-11-29 17:08 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>
>> A trigger will probably beat a rule for inserts/updates involving a small
>> number of rows.
>
> Which is exactly what partitioning is doing.
Say what?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
@ 2007-11-29 17:13 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-29 17:13 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thu, 29 Nov 2007 17:08:29 +0000
Gregory Stark <stark@enterprisedb.com> wrote:
>
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
> > Tom Lane wrote:
> >
> >> A trigger will probably beat a rule for inserts/updates involving
> >> a small number of rows.
> >
> > Which is exactly what partitioning is doing.
>
> Say what?
Heh, o.k. that was an ambiguous sentence. In a partitioned environment
you are likely not moving millions of rows around. Thus the "rule"
benefit is lost. You are instead performing many (sometimes
lots-o-many) inserts and updates that involve a small number of rows.
A trigger/function as Tom already pointed out is going to perform better
than a rule in that case. The benefit becomes even more pronounce the
more partitions you have.
Sincerely,
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHTvNXATb/zqfZUUQRAqeJAJ9CoO6F4zYJwY4geAR1UsvVCYqagwCdFJm9
63GRksppATp7fK9qu2RYXD0=
=VuWy
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 17:29 ` Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:52 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 38+ messages in thread
From: Gregory Stark @ 2007-11-29 17:29 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Heh, o.k. that was an ambiguous sentence. In a partitioned environment
> you are likely not moving millions of rows around. Thus the "rule"
> benefit is lost. You are instead performing many (sometimes
> lots-o-many) inserts and updates that involve a small number of rows.
I'm still not following at all. If you're partitioning it's because you have a
*lot* of data. It doesn't say anything about what you're doing with that data.
Partitioning is useful for managing large quantities of data for both OLTP and
DSS systems.
I tend to be happier recommending triggers over rules if only because rules
are just harder to understand. Arguably they don't really work properly for
this use anyways given what happens if you use volatile functions like
random() in your where clause.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
@ 2007-11-29 17:52 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 20:22 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-30 09:54 ` Re: [PATCHES] Partition: use triggers instead of rules Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 38+ messages in thread
From: Joshua D. Drake @ 2007-11-29 17:52 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thu, 29 Nov 2007 17:29:51 +0000
Gregory Stark <stark@enterprisedb.com> wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
> > Heh, o.k. that was an ambiguous sentence. In a partitioned
> > environment you are likely not moving millions of rows around. Thus
> > the "rule" benefit is lost. You are instead performing many
> > (sometimes lots-o-many) inserts and updates that involve a small
> > number of rows.
>
> I'm still not following at all. If you're partitioning it's because
> you have a *lot* of data. It doesn't say anything about what you're
If you have lots of data it doesn't mean you are modifying lots of
data. I took perhaps incorrectly what tgl said as modifying lots of
data. E.g; I am doing a large transaction that is going to
insert/update 500 thousand rows.
I don't think anyone here (good lord I hope not) would say that firing
a trigger over 500k rows is fast. Instead you should likely just work the data
outside the partition and then move it directly into the target
partition.
> doing with that data. Partitioning is useful for managing large
> quantities of data for both OLTP and DSS systems.
Certainly. I am not really arguing that and I would tend to agree that
I am being very focused on my arguments about partitioning. To me it is
obvious that you don't use triggers or rules when moving tons of data,
either one is just a burden you don't need.
Partitioning is generally most useful for:
Breaking up large tables and indexes so you are dealing with less data
on active queries.
Breaking up large tables so you don't end up vacuuming at 500 million
row table that only 1 million rows are ever updated.
Rotating out highly updated data so you can keep bloat down (HOT
resolves this in certain cases).
General data management of large sets. Archives and the like.
In any of the above cases a trigger is going to work better than a rule
with the exceptions of what TGL pointed out and in simpler
partitioning environments where the number of partitions are very low.
Either way, to drive this back to topic :).. on the docs if we keep the
rule example it should be below the partitioned example and we need to
list caveats on both. Which I personally think is overkill for
reference documentation but it seems to be what some people want.
>
> I tend to be happier recommending triggers over rules if only because
> rules are just harder to understand. Arguably they don't really work
> properly for this use anyways given what happens if you use volatile
> functions like random() in your where clause.
>
nod.
Sincerely,
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHTvxkATb/zqfZUUQRAhRtAKCHcWBhVZgTM8XXq8kJWWmi0m49cACgmAU5
xqosTo6sJPqpMMKWNvoTWGU=
=zDUw
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:52 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 20:22 ` Gregory Stark <stark@enterprisedb.com>
2007-11-30 07:04 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
1 sibling, 1 reply; 38+ messages in thread
From: Gregory Stark @ 2007-11-29 20:22 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
"Joshua D. Drake" <jd@commandprompt.com> writes:
> If you have lots of data it doesn't mean you are modifying lots of
> data.
It sure can. How do you modify lots of data if you *don't* have lots data in
the first place? Certainly it doesn't mean you necessarily are, some databases
are OLTP which do no large updates. But data warehouses with oodles of data
also often have to do large updates or deletions.
> I don't think anyone here (good lord I hope not) would say that firing
> a trigger over 500k rows is fast. Instead you should likely just work the data
> outside the partition and then move it directly into the target
> partition.
Well you don't even have to do that. You can issue the updates directly
against the partitions. In fact, that's precisely what the rules effectively
do... Rules rewrite the query to be a query directly against the partitions.
Come to think of it I think there actually is a correct way to use rules which
wouldn't suffer from the problems that have come up. Instead of putting a
WHERE clause on the rule just expand deletes and updates to expand to deletes
and updates against *all* partitions. Then let constraint_exclusion kick in to
narrow down which partitions should actually receive the updates and deletes.
I think triggers are the only solution for insert though.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [DOCS] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:52 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 20:22 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
@ 2007-11-30 07:04 ` NikhilS <nikkhils@gmail.com>
2007-12-01 04:56 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
0 siblings, 1 reply; 38+ messages in thread
From: NikhilS @ 2007-11-30 07:04 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
Hi,
On Nov 30, 2007 1:52 AM, Gregory Stark <stark@enterprisedb.com> wrote:
>
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
> > If you have lots of data it doesn't mean you are modifying lots of
> > data.
>
> It sure can. How do you modify lots of data if you *don't* have lots data
> in
> the first place? Certainly it doesn't mean you necessarily are, some
> databases
> are OLTP which do no large updates. But data warehouses with oodles of
> data
> also often have to do large updates or deletions.
>
> > I don't think anyone here (good lord I hope not) would say that firing
> > a trigger over 500k rows is fast. Instead you should likely just work
> the data
> > outside the partition and then move it directly into the target
> > partition.
>
> Well you don't even have to do that. You can issue the updates directly
> against the partitions. In fact, that's precisely what the rules
> effectively
> do... Rules rewrite the query to be a query directly against the
> partitions.
>
> Come to think of it I think there actually is a correct way to use rules
> which
> wouldn't suffer from the problems that have come up. Instead of putting a
> WHERE clause on the rule just expand deletes and updates to expand to
> deletes
> and updates against *all* partitions. Then let constraint_exclusion kick
> in to
> narrow down which partitions should actually receive the updates and
> deletes.
> I think triggers are the only solution for insert though.
>
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.
Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:52 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 20:22 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-30 07:04 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
@ 2007-12-01 04:56 ` David Fetter <david@fetter.org>
2007-12-02 19:21 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 38+ messages in thread
From: David Fetter @ 2007-12-01 04:56 UTC (permalink / raw)
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>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
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
Attachments:
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>
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:52 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 20:22 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-30 07:04 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-12-01 04:56 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
@ 2007-12-02 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 38+ messages in thread
From: Tom Lane @ 2007-12-02 19:21 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: NikhilS <nikkhils@gmail.com>; Gregory Stark <stark@enterprisedb.com>; Joshua D. Drake <jd@commandprompt.com>; Michael Paesold <mpaesold@gmx.at>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
David Fetter <david@fetter.org> writes:
> On Fri, Nov 30, 2007 at 12:34:05PM +0530, NikhilS wrote:
>> 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.
> Does my latest patch attached address this well enough?
Applied with revisions and extensions. (I take it you hadn't actually
tested the example :-()
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:08 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Re: Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Re: [DOCS] Partition: use triggers instead of rules Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:52 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-30 09:54 ` Simon Riggs <simon@2ndquadrant.com>
1 sibling, 0 replies; 38+ messages in thread
From: Simon Riggs @ 2007-11-30 09:54 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
On Thu, 2007-11-29 at 09:52 -0800, Joshua D. Drake wrote:
> In any of the above cases a trigger is going to work better than a
> rule with the exceptions of what TGL pointed out and in simpler
> partitioning environments where the number of partitions are very low.
Agreed to this and in general to JD's points.
Tom's point about Rules being statement-level is valid only when all
rows from an INSERT SELECT go into one partition. If that were the case
then it seems strange to spend lots of time designing a rules
infrastructure anyway.
If you try to do lots of partitions and RULEs then it sucks. I spoke
against their inclusion originally and do so again now. My point then
was you don't need this for most partitioning applications.
What nobody has mentioned is that Triggers suck as well, so neither
Triggers nor Rules should be given centre stage. COPY only makes sense
running into the table you are loading and if you're trying to load
large amounts of data using INSERTs + anything then you need a whack.
Rob did a beautiful de-construction of all of this in Montreal, BTW,
with humour too.
Current PostgreSQL partitioning is not the same as Oracle's and papering
over the cracks doesn't help anybody much. ISTM we should say to people
to use COPY into a named partition for high speed, plus these other
suggestions if you want some fancy logic, but go careful, cos they're
slow.
It would be nice to have an example of using CREATE TABLE LIKE + COPY in
same transaction, then ALTER TABLE ... INHERITS to add the partition
onto the main table. That is now the fastest way in 8.3.
I'll leave it to y'all from here though.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 38+ messages in thread
From: Tom Lane @ 2007-11-29 17:12 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>> A trigger
>> will probably beat a rule for inserts/updates involving a small number
>> of rows.
> Which is exactly what partitioning is doing.
Nonsense. Well, maybe *you* never do that, but if so you are hardly
reflective of the whole world.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: [PATCHES] Partition: use triggers instead of rules
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:53 ` Re: Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 02:58 ` Re: [DOCS] Partition: use triggers instead of rules Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` Re: [PATCHES] Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-29 05:55 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:41 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` Re: [DOCS] Partition: use triggers instead of rules NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Re: [DOCS] Partition: use triggers instead of rules Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:01 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:17 ` Re: [PATCHES] Partition: use triggers instead of rules Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Re: [PATCHES] Partition: use triggers instead of rules Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-29 17:19 ` Andrew Dunstan <andrew@dunslane.net>
3 siblings, 0 replies; 38+ messages in thread
From: Andrew Dunstan @ 2007-11-29 17:19 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Paesold <mpaesold@gmx.at>; NikhilS <nikkhils@gmail.com>; David Fetter <david@fetter.org>; Jonah H. Harris <jonah.harris@gmail.com>; pgsql-docs; PostgreSQL Patches <pgsql-patches@postgresql.org>
Joshua D. Drake wrote:
>
>> A trigger
>> will probably beat a rule for inserts/updates involving a small number
>> of rows.
>
> Which is exactly what partitioning is doing.
>
> For large numbers of rows, like an INSERT/SELECT from another
>> large table, the rule is likely to win, because its overhead is paid
>> once per query not once per row. Also, if you implement the trigger
>> with an EXECUTE (forcing a planning cycle) intead of hard-coded
>> commands, the speed advantage becomes even more dubious.
>
> Not for partitioning. Although I agree with your sentiments for normal
> operation.
>
>
Joshua, you're not making much sense here.
Tom is talking about partitioning and his analysis is correct *in the
partitioning case* AFAICS.
What basis do you have for saying he is not?
cheers
andrew
^ permalink raw reply [nested|flat] 38+ messages in thread
end of thread, other threads:[~2007-12-02 19:21 UTC | newest]
Thread overview: 38+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-28 20:26 Partition: use triggers instead of rules David Fetter <david@fetter.org>
2007-11-28 20:39 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-28 20:41 ` David Fetter <david@fetter.org>
2007-11-28 20:46 ` David Fetter <david@fetter.org>
2007-11-28 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-28 20:57 ` David Fetter <david@fetter.org>
2007-11-28 21:03 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-28 22:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 00:53 ` David Fetter <david@fetter.org>
2007-11-29 01:06 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-29 01:07 ` David Fetter <david@fetter.org>
2007-11-29 06:23 ` Jeremy Drake <pgsql@jdrake.com>
2007-11-29 02:58 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-11-29 03:17 ` David Fetter <david@fetter.org>
2007-11-29 05:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 06:36 ` David Fetter <david@fetter.org>
2007-11-29 06:41 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 15:05 ` NikhilS <nikkhils@gmail.com>
2007-11-29 15:29 ` Michael Paesold <mpaesold@gmx.at>
2007-11-29 15:48 ` Usama Dar <munir.usama@gmail.com>
2007-11-29 16:01 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:06 ` Michael Paesold <mpaesold@gmx.at>
2007-11-29 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 16:32 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 16:42 ` Bruce Momjian <bruce@momjian.us>
2007-11-29 16:49 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:52 ` David Fetter <david@fetter.org>
2007-11-29 17:08 ` Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:13 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 17:29 ` Gregory Stark <stark@enterprisedb.com>
2007-11-29 17:52 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-29 20:22 ` Gregory Stark <stark@enterprisedb.com>
2007-11-30 07:04 ` NikhilS <nikkhils@gmail.com>
2007-12-01 04:56 ` David Fetter <david@fetter.org>
2007-12-02 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-30 09:54 ` Simon Riggs <simon@2ndquadrant.com>
2007-11-29 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-29 17:19 ` Andrew Dunstan <andrew@dunslane.net>
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