3

I have a column called updated which is intended to show the last time that column was altered.

My first attempt at this was to create a trigger that changed the updated column to the value returned by now(). But because this trigger happens on an update event, it caused an infinite loop (updating the updated column causes the trigger to fire).

I also tried implementing a rule to do this with similar effects.

I can't imagine that this is something I am forced to do on the application layer when ever I call and update function. So how can I update that row's updated column without causing infinite loops?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
user3245442
  • 43
  • 1
  • 8
  • 1
    http://stackoverflow.com/a/26284695/2101267 – Dark Falcon Feb 12 '15 at 18:33
  • Why would changing a column in a trigger cause an infinite loop? I don't see how `new.some_column := now()` could ever do that. –  Feb 12 '15 at 19:23
  • I believe because the trigger fires on update. So when update my_table SET updated = now(); executes it is updating the row, which then causes the update trigger to fire again, and update the row again, and loops in that cycle. – user3245442 Feb 12 '15 at 20:13
  • possible duplicate of [How do I automatically update a timestamp in PostgreSQL](http://stackoverflow.com/questions/9556474/how-do-i-automatically-update-a-timestamp-in-postgresql) – Jonathan Hall Feb 12 '15 at 20:35
  • Well, you shouldn't be running an `UPDATE` on the table that is being "triggered" - and you don't need that in the first place. Just assign the new value to the row. See Flimzy's answer. That is really the only correct way to do it. –  Feb 13 '15 at 06:47

2 Answers2

11

Use a trigger like this:

CREATE OR REPLACE FUNCTION update() RETURNS trigger AS $$
BEGIN
    IF NEW.updated = OLD.updated THEN
        NEW.updated = NOW()
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER table_update
BEFORE UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE update()

This way you aren't in a loop--you only update the value once (before the UPDATE is executed), and you also don't clobber the value, if for some reason you want to set updated explicitly (as in when importing old data from backup, for instance).

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
2

I have solved this in other applications by checking the field I am changing and if nothing is changed, then I do not do the update. If you can check the updated column and if it is within the last N seconds, do not do the update. This should stop the infinite loop. Pick the number you want for N, so you can know the update timestamp is always within N seconds.

Tim
  • 100
  • 1
  • 4
  • 1
    No offence, but this answer gives me the impression that you are doing something fundamentally wrong in your trigger. –  Feb 12 '15 at 19:25
  • No offence taken. My answer is based on external code from an event trigger, not sql. It was the only way to solve the issue. – Tim Feb 12 '15 at 22:17