Re: Another perplexity with PG rules

"Ken Winter" <ken(at)sunward(dot)org> 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