Re: TODO: trigger features

Tom Lane wrote:

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