"Ken Winter" <ken@xxxxxxxxxxx> writes: > After trying about a million things, I'm wondering about the meaning of > "OLD." as the actions in a rule are successively executed. What I have done > assumes that: > ... > (b) The "OLD." values that appear in the second (INSERT) action in the rule > are not changed by the execution of the first (UPDATE) rule. I believe this is mistaken. OLD is effectively a macro for "the existing row(s) satisfying the rule's WHERE clause". You've got two problems here --- one is that the UPDATE may have changed the data in those rows, and the other is that the UPDATE may cause them to not satisfy the WHERE clause anymore. > (c) Whatever the truth of the above assumptions, the second (INSERT) action > in the 'on_update_2_preserve_h' rule should insert SOMEthing. See above. If no rows remain satisfying WHERE, nothing will happen. > How to make this whole thing do what is required? I'd suggest seeing if you can't do the INSERT first then the UPDATE. This may require rethinking which of the two resulting rows is the "historical" one and which the "updated" one, but it could probably be made to work. Also, you might think about keeping the historical info in a separate table (possibly it could be an inheritance child of the master table). This would make it easier to distinguish the historical and current info when you need to. Lastly, I'd advise using triggers not rules wherever you possibly can. In particular, generation of the historical-log records would be far more reliable if implemented as an AFTER UPDATE trigger on the base table. (Over the years I've gotten less and less satisfied with Postgres' rules feature --- it just seems way too hard to make it do what people want reliably. I'm afraid there's not much we can do to fix it without creating an enormous compatibility problem unfortunately :-(. But by and large, triggers are a lot easier for people to wrap their brains around, once they get over the notational hurdle of having to write a trigger function. I'd like to see us allow triggers on views, and then maybe rules could fade into the sunset for any but the most abstruse applications.) regards, tom lane
