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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
