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

From Tom Lane
Subject Re: TODO: trigger features
Date
Msg-id 12200.1060114974@sss.pgh.pa.us
Whole thread Raw
In response to Re: TODO: trigger features  (Andreas Pflug <pgadmin@pse-consulting.de>)
Responses Re: TODO: trigger features
List pgsql-hackers
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?

> 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


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Adjustment of spinlock sleep delays
Next
From: Tom Lane
Date:
Subject: Re: logging stuff