Re: TODO: trigger features - Mailing list pgsql-hackers

Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>  
>
>>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
>>    
>>
>
>Why would the "not touched" case need to change anything?
>
Only interesting on insert, using DEFAULT together with the other rules 
can handle this.

>>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.
>>    
>>
>
>That's not an argument for SET ts=ts.  There are many possible kluges
>for detecting whether an update came from a trigger or directly from the
>user, and using ts=ts is only one (not a very appealing one either IMHO).
>
>The most obvious alternative is to have an additional boolean column
>"from_trigger" defaulting to FALSE.  The trigger that sets the
>timestamp can do this:
>
>    if new.from_trigger then
>        new.from_trigger = false;
>    else
>        new.timestamp = now();
>
>Then, the stored value of from_trigger is always false, and any update
>will cause the timestamp column to get updated --- unless the update
>explicitly sets from_trigger=true.  This would also provide a solution
>for your other concern about being able to override the timestamp on
>insert.
>
I wonder why you are suggesting workarounds for features that other 
databases provide. Of course inventing a "I intend to change that row" 
flag is a way, but why not providing this directly? Might not be too 
easy, I know.

>>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.
>>    
>>
>
>Yet another messy kluge :-(.
>  
>
YATS (yet another TODO suggestion):
provide an official and reliable way to temporarily enable/disable triggers.
"ALTER TABLE xxx ENABLE/DISABLE TRIGGER ALL/trgName"

We still have that nasty "not presently checked everywhere it should be" 
comment in the doc for pg_trigger...
Yes, this could be achieved by dropping and recreating the trigger after 
importing, which I expect to be suggested by you ;-)


Regards,
Andreas