Re: TODO: trigger features

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?

> 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.

> 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 :-(.

regards, tom lane