Re: Rule/currval() issue

"Creager, Robert S" <CreagRS(at)LOUISVILLE(dot)STORTEK(dot)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