Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
Michael Paesold wrote: NikhilS wrote: If you have a partitioning setup that uses rules please refer to the 8.2 documentation on partitioning +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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Rules are extremely slow in comparisons and not anywhere near as > flexible. As I said up post yesterday... they work well in the basic > partitioning configuration but anything else they are extremely deficient. I think that the above claim is exceedingly narrow-minded. 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
Joshua D. Drake wrote: > Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> Rules are extremely slow in comparisons and not anywhere near as > >> flexible. As I said up post yesterday... they work well in the basic > >> partitioning configuration but anything else they are extremely deficient. > > > > I think that the above claim is exceedingly narrow-minded. > > We are talking about partitioning. It is supposed to be narrow-minded. Sure, but look at all the confusion we have had just on this list about it. We had better state why triggers should be used in place of rules _for_ _partitioning_ or that confusion will continue. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Rules are extremely slow in comparisons and not anywhere near as flexible. As I said up post yesterday... they work well in the basic partitioning configuration but anything else they are extremely deficient. I think that the above claim is exceedingly narrow-minded. We are talking about partitioning. It is supposed to be narrow-minded. 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
Bruce Momjian wrote: Joshua D. Drake wrote: Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Rules are extremely slow in comparisons and not anywhere near as flexible. As I said up post yesterday... they work well in the basic partitioning configuration but anything else they are extremely deficient. I think that the above claim is exceedingly narrow-minded. We are talking about partitioning. It is supposed to be narrow-minded. Sure, but look at all the confusion we have had just on this list about it. We had better state why triggers should be used in place of rules _for_ _partitioning_ or that confusion will continue. Sure I have no problem with that. Joshua D. Drake ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
"Joshua D. Drake" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] Partition: use triggers instead of rules
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 29 Nov 2007 17:08:29 + Gregory Stark <[EMAIL PROTECTED]> wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> 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- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 29 Nov 2007 17:29:51 + Gregory Stark <[EMAIL PROTECTED]> wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> 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- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Thu, Nov 29, 2007 at 11:42:18AM -0500, Bruce Momjian wrote: > Joshua D. Drake wrote: > > Tom Lane wrote: > > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > >> Rules are extremely slow in comparisons and not anywhere near as > > >> flexible. As I said up post yesterday... they work well in the basic > > >> partitioning configuration but anything else they are extremely > > >> deficient. > > > > > > I think that the above claim is exceedingly narrow-minded. > > > > We are talking about partitioning. It is supposed to be narrow-minded. > > Sure, but look at all the confusion we have had just on this list about > it. We had better state why triggers should be used in place of rules > _for_ _partitioning_ or that confusion will continue. Please find enclosed a patch with use cases for each. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 29 Nov 2007 17:51:46 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex trigger function as shown ! below: ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate >= DATE '2004-0
Re: [DOCS] PITR Doc clarifications
On Wed, 2007-11-28 at 17:36 -0500, Bruce Momjian wrote: > Slightly modified patch attached and applied. Thanks. Nice additions, thanks. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
