Re: [GENERAL] Rules vs Triggers
Thanks for the info guys; got a better understanding now. -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Rules vs Triggers
Am Dienstag, 26. Juli 2005 23:53 schrieb Randall Perry: Read the Rules section of the manual and the section on Rules vs Triggers. From what I get triggers are necessary for column constraints. As far as speed, it seems there are some differences between how fast rules/triggers would do the same action, but that some complex analysis is involved to determine this. And I gathered rules are necessary to allow update/insert/delete actions on views. Can anyone give me some simple reasons why they choose rules over triggers in their real-world dbs? Triggers are executed per row, so they are quite procedural. If you insert or update 500 rows they are fired 500 times. Rules modify the sql query tree. So rules are at some point nothing else as rewrites to your sql statement. If you update 500 rows and you have an on update rule. Your query tree is modified once and gets executed for all 500 rows. Rules are much faster an much more relational than triggers are, because they become pure sql before they reach the database. imagine an on delete trigger which record the deletion in an audit table like this: create trigger tg_member before delete on member for each row EXECUTE PROCEDURE audit_meber_deletion(); audit_meber_deletion() does an INSERT to an audit table. no think of members are organized in groups. If you delete a group ALL members are deleted because of cascading foreing keys references. Now delete a group with 2 members. The trigger is fired 2 times No Imagine a rule which does create rule rl_member AS ON DELETE TO member DO INSERT INTO member_deletion (membername) VALUES (OLD.membername) this is executed once and is as fast as SQL can be. Normally you dont see a difference between triggers and rules if you have update and insert statemnts which affect only a few rows. but if it comes to affecting many rows, you should use rules. But rules are more difficult to understand. kind regards, janning ---(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: [GENERAL] Rules vs Triggers
# [EMAIL PROTECTED] / 2005-07-26 17:53:35 -0400: Read the Rules section of the manual and the section on Rules vs Triggers. From what I get triggers are necessary for column constraints. As far as speed, it seems there are some differences between how fast rules/triggers would do the same action, but that some complex analysis is involved to determine this. And I gathered rules are necessary to allow update/insert/delete actions on views. Can anyone give me some simple reasons why they choose rules over triggers in their real-world dbs? Something like this will ensure the user will not be able to modify the author information in updatedon/updatedby columns: CREATE TABLE t1 ( id SERIAL, val TEXT, updatedon TIMESTAMP, updatedby TEXT ); CREATE VIEW v1 AS SELECT * FROM t1; CREATE RULE v1i AS ON INSERT TO v1 DO INSTEAD INSERT INTO t1 (val, updatedon, updatedby) VALUES (NEW.val, NOW(), CURRENT_USER); CREATE RULE v1u AS ON UPDATE TO v1 DO INSTEAD UPDATE t1 SET val = NEW.val, updatedon = NOW(), updatedby = CURRENT_USER WHERE id = NEW.id; (That should be taken as pseudocode, I'm sure there are bugs in it.) Another common reason is the need/desire to keep values of certain columns somehow synchronized, as in: CREATE FUNCTION UNIXTS_TO_SQLTS(INTEGER) RETURNS TIMESTAMP AS ...; CREATE TABLE t2 ( id SERIAL, unixts INTEGER, sqlts TIMESTAMP ); CREATE VIEW v2 AS SELECT * FROM t2; CREATE RULE v2i AS ON INSERT TO v2 DO INSTEAD INSERT INTO t2 (unixts, sqlts) VALUES (NEW.unixts, UNIXTS_TO_SQLTS(NEW.unixts); CREATE RULE v2u AS ON UPDATE TO v2 DO INSTEAD UPDATE t2 SET unixts = NEW.unixts, sqlts = UNIXTS_TO_SQLTS(NEW.unixts), WHERE id = NEW.id; So basically, it's these reasons: * to have updatable views - so you don't select from view_x, but insert into table_x; - if updating certain view involves updating more than one table, you'll want to have the code fixated in a rule to tighten the space where clients can screw up * to prevent clients from updating certain columns and/or rows * to enforce certain characteristics of data -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(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
