You are correct Tom, vtquotehistory is a view of tquotehistory, and I am
trying to log in this example. Sorry I didn't point that out. The view is a
straight view with no WHERE clause or criteria of any kind. The UPDATE does
cause the OLD record to become updated to whatever changes have been set as
needed in the NEW record. In that sense, the OLD record is discarded, but my
code sometimes assumes the keyword OLD is still a valid reference even after
an UPDATE. Is that my problem?
ARGGHH!!! I just re-read 9.2.1. Read Committed Isolation Level and I am
screwed! It was my understanding that all the queries in parenthesis in a
RULE were evaluated within a transaction, and that that meant that all
queries saw the same snapshot of the database and changes were committed all
at once at the end. I see now that queries within a transaction are only
ever free from seeing the COMMITs of _other_ transactions, and even that is
only for SERIALIZABLE transactions.
I've got over twenty rules with multiple updates within them that could be
affecting each other in ways I haven't evaluated. I've got my work cut out
for me. You're a lifesaver Tom, thanks.
-------------------------------------------
jtocci
Fort Wayne, IN
PS - I can't abandon my beautiful rules for triggers just yet :-) With only
five pages of code, all rules, I've built a replacement for the DOS
application we use (soon to be 'used') to keep track of inventory,
shipping/receiving, purchasing and manufacturing('build' from a bill of
materials).
-------------------------------------------
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Hm. Am I right in supposing that vtquotehistory is a view on
tquotehistory? Does the UPDATE cause the row that was visible in the
view to be no longer visible in the view (or at least not matched by the
constraints on the original UPDATE command)? If so, that's your problem
--- the "old" references in the INSERT will no longer find any matching
row in the view.
If your goal is to log operations on tquotehistory, my recommendation is
to forget about views and rules and just use a trigger on tquotehistory.
Triggers are *way* easier to understand, even if the notation looks
worse.
regards, tom lane
