pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRules and Triggers
8+ messages / 6 participants
[nested] [flat]
* Rules and Triggers
@ 2002-05-16 04:59 Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-05-16 15:12 ` Re: Rules and Triggers Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ludwig Lim @ 2002-05-16 04:59 UTC (permalink / raw)
To: pgsql-sql
Hi:
Are there cases when triggers are faster than rules?
It appears that on the database that I used; rules
used sequential scan while triggers used index scan.
I had a table "sc_redeem". There is a rule which I
place on that table. The body of rule is "
CREATE RULE rule_update_redeem as
ON insert TO sc_redeem
DO
UPDATE sc_customer_card
SET redeemed = redeemed + new.points,
balance = balance - new.points
WHERE customer_id = new.customer_id AND
card_type_cd = new.card_type_cd;"
The index of sc_customer_card is on the field
"customer_id". I noticed when I use the SQL statement.
EXPLAIN INSERT INTO sc_redeem(....) VALUES (....);
It reported that it performed a sequential scan on the
table sc_customer_card (around 100,000+ records w/
unique customer id for each record).
I tried converting the rule to function & trigger
and it reported that it used an Index scan given the
same INSERT statement.
I was wondering why the rule used sequentail scan
and the equivalent function trigger used index scan.
Thank you,
ludwig lim
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Rules and Triggers
2002-05-16 04:59 Rules and Triggers Ludwig Lim <lud_nowhere_man@yahoo.com>
@ 2002-05-16 15:12 ` Josh Berkus <josh@agliodbs.com>
2002-05-17 18:19 ` Re: Rules and Triggers Jan Wieck <janwieck@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2002-05-16 15:12 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; pgsql-sql
Ludwig,
> I tried converting the rule to function & trigger
> and it reported that it used an Index scan given the
> same INSERT statement.
>
> I was wondering why the rule used sequentail scan
> and the equivalent function trigger used index scan.
This is one of the documented differences between rules and triggers
(at least, I've seen it in the documentation *somewhere*). I can't
tell you "why", though ... hopefully Tom or Bruce will speak to that.
For myself, I use triggers for most things, and rules just for
updatable views. Aside for index efficiency, rules are a Postgres-only
thing, whereas several RDBMS's support triggers. Gives me marginally
better portability.
-Josh Berkus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Rules and Triggers
2002-05-16 04:59 Rules and Triggers Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-05-16 15:12 ` Re: Rules and Triggers Josh Berkus <josh@agliodbs.com>
@ 2002-05-17 18:19 ` Jan Wieck <janwieck@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jan Wieck @ 2002-05-17 18:19 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Ludwig Lim <lud_nowhere_man@yahoo.com>; pgsql-sql
Josh Berkus wrote:
> Ludwig,
>
> > I tried converting the rule to function & trigger
> > and it reported that it used an Index scan given the
> > same INSERT statement.
> >
> > I was wondering why the rule used sequentail scan
> > and the equivalent function trigger used index scan.
>
> This is one of the documented differences between rules and triggers
> (at least, I've seen it in the documentation *somewhere*). I can't
> tell you "why", though ... hopefully Tom or Bruce will speak to that.
Neither is *that* one of the differences between rules and
triggers, nor *should* something like that be documented
instead of fixed.
There might be a difference in the selectivity when using
OLD/NEW Var nodes vs. $n parameters, and therefore a
different estimate by the optimizer. But in general rule
generated queries are using indexes.
Of course, that requires up to date statistics. Are the
tables ANALYZE'd properly?
> For myself, I use triggers for most things, and rules just for
> updatable views. Aside for index efficiency, rules are a Postgres-only
> thing, whereas several RDBMS's support triggers. Gives me marginally
> better portability.
As long as your queries only affect very few rows, you'll not
notice much of a performance difference. Try updating a
couple thousand rows with one UPDATE. A trigger will fire a
couple thousand times, where a rule does all the work in one
additional query.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 8+ messages in thread
* Rules and Triggers
@ 2002-09-10 18:52 Michael Paesold <mpaesold@gmx.at>
2002-09-10 19:24 ` Re: Rules and Triggers Adam Erickson <adamre@cox.net>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Paesold @ 2002-09-10 18:52 UTC (permalink / raw)
To: pgsql-sql
Hi all,
can you tell me in what order rules and triggers are executed?
First, what comes first, the rules, or the triggers?
And then, in what order are all the rules / triggers executed?
Regards,
Michael Paesold
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Rules and Triggers
2002-09-10 18:52 Rules and Triggers Michael Paesold <mpaesold@gmx.at>
@ 2002-09-10 19:24 ` Adam Erickson <adamre@cox.net>
2002-09-10 19:45 ` Re: Rules and Triggers Michael Paesold <mpaesold@gmx.at>
0 siblings, 1 reply; 8+ messages in thread
From: Adam Erickson @ 2002-09-10 19:24 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; pgsql-sql
Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
Triggers are carried out after the SQL is executed and the data is modified.
So, to answer your question, I think rules come first. As to which order
the rules / triggers are executed probably depends on the order you put them
in when you created them but I pulled that straight out of my arse. :)
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Michael Paesold
> Sent: Tuesday, September 10, 2002 1:53 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Rules and Triggers
>
>
> Hi all,
>
> can you tell me in what order rules and triggers are executed?
>
> First, what comes first, the rules, or the triggers?
> And then, in what order are all the rules / triggers executed?
>
> Regards,
> Michael Paesold
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Rules and Triggers
2002-09-10 18:52 Rules and Triggers Michael Paesold <mpaesold@gmx.at>
2002-09-10 19:24 ` Re: Rules and Triggers Adam Erickson <adamre@cox.net>
@ 2002-09-10 19:45 ` Michael Paesold <mpaesold@gmx.at>
2002-09-10 23:28 ` Re: Rules and Triggers Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Paesold @ 2002-09-10 19:45 UTC (permalink / raw)
To: Adam Erickson <adamre@cox.net>; pgsql-sql
Adam Erickson wrote:
> Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> Triggers are carried out after the SQL is executed and the data is
modified.
> So, to answer your question, I think rules come first. As to which order
> the rules / triggers are executed probably depends on the order you put
them
> in when you created them but I pulled that straight out of my arse. :)
That would be correct for triggers with AFTER INSERT/UPDATE/DELETE.
But what about triggers BEFORE INSERT/UPDATE/DELETE?
Regards,
Michael
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Rules and Triggers
2002-09-10 18:52 Rules and Triggers Michael Paesold <mpaesold@gmx.at>
2002-09-10 19:24 ` Re: Rules and Triggers Adam Erickson <adamre@cox.net>
2002-09-10 19:45 ` Re: Rules and Triggers Michael Paesold <mpaesold@gmx.at>
@ 2002-09-10 23:28 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-11 07:28 ` Re: Rules and Triggers Michael Paesold <mpaesold@gmx.at>
0 siblings, 1 reply; 8+ messages in thread
From: Ross J. Reedstrom @ 2002-09-10 23:28 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; +Cc: Adam Erickson <adamre@cox.net>; pgsql-sql
On Tue, Sep 10, 2002 at 09:45:16PM +0200, Michael Paesold wrote:
>
> Adam Erickson wrote:
>
> > Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> > Triggers are carried out after the SQL is executed and the data is
> modified.
> > So, to answer your question, I think rules come first. As to which order
> > the rules / triggers are executed probably depends on the order you put
> them
> > in when you created them but I pulled that straight out of my arse. :)
>
> That would be correct for triggers with AFTER INSERT/UPDATE/DELETE.
> But what about triggers BEFORE INSERT/UPDATE/DELETE?
An important thing to remember about rules vs. triggers: Rules operate
on an SQL _query_ that involves the table (or view) that the rule is
attached to, whereas triggers operate on the individual _tuples_ of the
table they are attached to. Rules act to rewrite the incoming query,
at the very earliest step: just after parsing, but before planning and
optimizing. So rules _always_ act first. Triggers act when storage gets
hit: when the optimizer has choosen a plan, and passes it to the executor
for, well, execution.
Ross
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Rules and Triggers
2002-09-10 18:52 Rules and Triggers Michael Paesold <mpaesold@gmx.at>
2002-09-10 19:24 ` Re: Rules and Triggers Adam Erickson <adamre@cox.net>
2002-09-10 19:45 ` Re: Rules and Triggers Michael Paesold <mpaesold@gmx.at>
2002-09-10 23:28 ` Re: Rules and Triggers Ross J. Reedstrom <reedstrm@rice.edu>
@ 2002-09-11 07:28 ` Michael Paesold <mpaesold@gmx.at>
0 siblings, 0 replies; 8+ messages in thread
From: Michael Paesold @ 2002-09-11 07:28 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: Adam Erickson <adamre@cox.net>; pgsql-sql
Ross J. Reedstrom wrote:
> An important thing to remember about rules vs. triggers: Rules operate
> on an SQL _query_ that involves the table (or view) that the rule is
> attached to, whereas triggers operate on the individual _tuples_ of the
> table they are attached to. Rules act to rewrite the incoming query,
> at the very earliest step: just after parsing, but before planning and
> optimizing. So rules _always_ act first. Triggers act when storage gets
> hit: when the optimizer has choosen a plan, and passes it to the executor
> for, well, execution.
>
> Ross
Thanks alot, again I understand a little bit more.
You're great, guys!
Regards, Michael Paesold
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-09-11 07:28 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-16 04:59 Rules and Triggers Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-05-16 15:12 ` Josh Berkus <josh@agliodbs.com>
2002-05-17 18:19 ` Jan Wieck <janwieck@yahoo.com>
2002-09-10 18:52 Rules and Triggers Michael Paesold <mpaesold@gmx.at>
2002-09-10 19:24 ` Adam Erickson <adamre@cox.net>
2002-09-10 19:45 ` Michael Paesold <mpaesold@gmx.at>
2002-09-10 23:28 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-11 07:28 ` Michael Paesold <mpaesold@gmx.at>
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