Re: TODO: trigger features

Tom Lane wrote:

>Andreas Pflug <pgadmin(at)pse-consulting(dot)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