From: Tom Lane <tgl@sss.pgh.pa.us>
To: Creager, Robert S <CreagRS@LOUISVILLE.STORTEK.COM>
Cc: 'SQL - PGSQL' <pgsql-sql@postgresql.org>
Subject: Re: Rule/currval() issue
Date: Wed, 14 Mar 2001 15:08:33 -0500
Message-ID: <7448.984600513@sss.pgh.pa.us> (raw)
In-Reply-To: <10FE17AD5F7ED31188CE002048406DE8514D00@lsv-msg06.stortek.com>
References: <10FE17AD5F7ED31188CE002048406DE8514D00@lsv-msg06.stortek.com>
"Creager, Robert S" <CreagRS@LOUISVILLE.STORTEK.COM> writes:
> You indicate trigger, rather than rule. Going by Momjian's book, he
> indicates that rules are "...ideal for when the action affects other
> tables." Can you clarify why you would use a trigger for this?
Primarily because there's a need to get at the default values that will
be computed for the inserted tuple (ie, the serial number it will be
assigned). An ON INSERT trigger has access to those values because it's
handed the fully-constructed tuple. A rule does not. QED.
My take on the rule vs. trigger issue is a little different from
Bruce's. To me, a trigger is ideal for actions that you want to drive
off insertion/deletion/update of individual tuples --- ie, all that you
need to look at to know what to do is the single tuple being processed.
However, that's also a trigger's weak spot: it will be fired again,
separately, for every inserted/deleted/updated tuple. In contrast,
a rule specifies a transformation of the original query, which makes it
good for bulk operations.
For example: suppose I'm deleting a whole ton of tuples in table A, say
delete from a where a.date < '2000-01-01'
and my application logic dictates that associated tuples in table B also
go away. If I make that happen with a trigger then I'll be executing
something like
delete from b where b.id = old.id
separately for each deleted A tuple. That amounts to a nested-loop join
between A and B, since B is scanned separately (hopefully with an
indexscan!) for each A tuple. On the other hand I could write a rule
on delete to a do
delete from b where b.id = old.id
This will expand my above query into
delete from b where b.id = a.id and a.date < '2000-01-01';
delete from a where a.date < '2000-01-01';
Now the planner can turn the B delete into a merge or hash join between
A and B. For large numbers of tuples that could make for a huge
speedup.
So basically, rules are good for specifying bulk operations between
related tables, whereas a trigger is good for more "retail" kinds of
things. Also, I think a trigger is a lot easier to understand, even
if there's a little more learning curve involved to write one (because
you also have to know some plpgsql). The transformational nature of
rules is harder to get a handle on; they seem trivial but they're really
not.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, CreagRS@LOUISVILLE.STORTEK.COM
Subject: Re: Rule/currval() issue
In-Reply-To: <7448.984600513@sss.pgh.pa.us>
References: <10FE17AD5F7ED31188CE002048406DE8514D00@lsv-msg06.stortek.com> <7448.984600513@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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