Andreas Pflug <[EMAIL PROTECTED]> writes:Only interesting on insert, using DEFAULT together with the other rules can handle this.
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?
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.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 :-(.
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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
