Re: Odd on-update inconsistency

Steve Crawford wrote:
> I'm having trouble understanding something I saw in my data from
> yesterday involving an inconsistency between values in a table and its
> associated rule-updated log table.
>
> For application debugging purposes (effectiveness of web double-submit
> suppression) we have a rule that creates an entry in a log table
> whenever the table we are watching is updated:

Ah, I think you'll find you don't. You have a rule that looks at first
glance like it *should* add an entry to your log table.

Rules rewrite the query like a macro would and OLD and NEW don't refer
to a row but to the entire set of rows. The most common problems you'll
see are related to:
1. nextval() / currval() not behaving like you'd think.
2. in particular with multiple-row updates or inserts

See the mailing list archives for plenty of discussion, and I think the
current manuals have a better description of rules than there used to be.

For inserting to a log table you'll want a trigger.

--
  Richard Huxton
  Archonet Ltd