Re: TODO: trigger features

Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>  
>
>>Tom Lane wrote:
>>    
>>
>>>This can already be done by comparing old and new values, no?
>>>
>>>      
>>>
>>No, this is not the case.
>>    
>>
>
>  
>
>>UPDATE foo SET x=x, y=y
>>is different from
>>UPDATE foo SET y=y
>>if triggers maintaining x are involved.
>>    
>>
>
>Only for what I would call extremely weird semantics of the triggers.
>
>If a trigger preceding yours did the same action (assigned x to itself),
>would you consider that something you needed to track?  If so, how would
>you find out about it?  You couldn't.
>
>If you want me to believe that the above is an important requirement,
>you'd better convince me that it's sane, because I don't think so.
>
I'm talking about a real life problem, and the upper sample was just an 
essence.

Consider this:
Table with one column that is maintained by a trigger for this rule:
- Only one row in a group of rows may have a foo-value of "true", all 
others must be "false".
- If foo=true is inserted/updated, other members of that data group must 
be set to false.
- If foo=false, designate one row for foo=true
- If not touched, use true if first member of that group, or false


This can be maintained by a trigger, but it will possibly trigger itself 
recursively (but doesn't need a second recursion)

Now we have another column: ts timestamp, that should contain the 
timestamp when the row was inserted/updated the last time by the *user*, 
not the trigger which is considered to work in the background. On 
INSERT, a DEFAULT current_timestamp will be the selected option, on 
UPDATE you would use NEW.TS := current_timestamp. But how to update the 
row, and retain the old timestamp value? Normally, a user's query 
wouldn't touch the ts column at all, leaving it to the backend to insert 
the correct values. But in the "maintain foo" trigger case, we could use 
"SET ts=ts" to signal to the trigger that we explicitely want to set the 
value. Same applies for the import case, when we want to insert a ts 
value coming from elsewhere but not from the trigger. This could also be 
done if there was something like "UPDATE ... WITH OPTION 
NOTRIGGER(trg_update_timestamp)" or so.

Regards,
Andreas