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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: ldm(at)apartia(dot)com, pgsql-general(at)hub(dot)org
Subject: Re: rules on INSERT can't UPDATE new instance?
Date: 2000-06-13 08:01:12
Message-ID: 2622.960883272@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message kurt miller 2000-06-13 08:32:35 feature request
Previous Message Bruce Momjian 2000-06-13 07:50:49 Re: rules on INSERT can't UPDATE new instance?

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2000-06-13 08:04:15 Re: trim() spec
Previous Message Tom Lane 2000-06-13 07:58:43 Re: Proposal for fixing numeric type-resolution issues