Re: Odd on-update inconsistency

Richard Huxton wrote:
> 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.
>
>
Hmmm. I was aware of certain issues with rules but in this case we have
no sequences/nextval()/currval() issues and, except for period-start
resets of certain columns, the normal update query only operates on a
single row (increment count for a given location) - and I reverified
that the key column really is unique.

It's not a big problem (this project ends in a month anyway). I just
want to increase my understanding to avoid future foot-gun potential as
I hadn't seen how our current setup would cause this type of issue. I
guess if it's critical that it works, I'll just write a trigger but
rules are quicker and easier.

Cheers,
Steve