Re: rules on INSERT can't UPDATE new instance?

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Is the INSERT rule re-ordering mentioned a TODO item?

Darn if I know. I threw the thought out for discussion, but didn't
see any comments. I'm not in a hurry to change it, unless there's
consensus that we should.

regards, tom lane

>> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>>>> I thought an INSERT rule with an UPDATE action would work on the same
>>>> table, but that fails. Seems the rule is firing before the INSERT
>>>> happens.
>>
>> Yes, a trigger is the right way to do surgery on a tuple before it is
>> stored. Rules are good for generating additional SQL queries that will
>> insert/update/delete other tuples (usually, but not necessarily, in
>> other tables). Even if it worked, a rule would be a horribly
>> inefficient way to handle modification of the about-to-be-inserted
>> tuple, because (being an independent query) it'd have to scan the table
>> to find the tuple you are talking about!
>>
>> The reason the additional queries are done before the original command
>> is explained thus in the source code:
>>
>> * The original query is appended last if not instead
>> * because update and delete rule actions might not do
>> * anything if they are invoked after the update or
>> * delete is performed. The command counter increment
>> * between the query execution makes the deleted (and
>> * maybe the updated) tuples disappear so the scans
>> * for them in the rule actions cannot find them.
>>
>> This seems to make sense for UPDATE/DELETE, but I wonder whether
>> the ordering should be different for the INSERT case: perhaps it
>> should be original-query-first in that case.