Re: Backend bug applying insert rules?

Luis Miguel <lmc(at)nova(dot)es> writes:
> When the rules applies as on insert the last row inserted isn't
> processed.

I think you are misunderstanding the way rules work. A trigger would
be a much more natural (and efficient!) way of accomplishing what you
are trying to do here.

The thing you have to remember about an ON INSERT rule is that it
doesn't apply to just the particular row you inserted. It causes the
given additional queries to be executed over the whole table! In the
example as you give it, each insert will cause the following additional
actions to be taken:

insert into expurgados select
capturas.codigo, 'processed', capturas.fecha
from capturas
where capturas.destino=1 and capturas.ct=false;
update capturas set ct=true
where capturas.destino=1 and capturas.ct=false;

*plus* similar queries derived from your other two rules. This will
start to get slow when capturas gets large.

Finally, I believe that these operations are executed *before* the
insert occurs, not after. So what's actually happening is that the
changes you think are being triggered by an insert don't happen until
the next insert.

Rules are good for global updates but not so hot for transforming a
single insert into some other operation. Use triggers instead, you'll
be much happier.

BTW, doing the rule-invoked operations first seems reasonable for
ON UPDATE and ON DELETE operations, but I am inclined to think it is a
bug (or at least bad design) for ON INSERT to work that way. Seems like
ON INSERT rules ought to execute after the INSERT, not before. Jan, any
comments?

regards, tom lane