Search Postgresql Archives

Re: Rules vs Triggers

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 20000 members. The trigger is fired 20000 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux