DEFAULT TIMESTAMP and UPDATE with no changes

[hansg]

Is there a database option or workaround that changed a column with default timestamp, only when a column in the row is really changed?

For example: UPDATE employees SET name = 'Mr. John' WHERE id = 1 ; That update shows in a translated log, as expected and ok: UPDATE name = 'Mr. John', my_timestamp_col = '2015-xxxxxxx...' WHERE id = 1 ;

When the same SQL update is executed another time I see this in the translated log: UPDATE my_timestamp_col = '2015-xxxxxxx...' WHERE id = 1 ;

Because the row is not changed at all the timestamp should not be set. Now we get a lot of updates that only set the timestamp column and a much more uncomitted rows.

[MarkCulp · Accepted solution]

There is no option to change the behaviour of DEFAULT TIMESTAMP.

Your alternative solution would be to change the column to be DEFAULT CURRENT TIMESTAMP and then write a BEFORE UPDATE trigger that checks if any of the values are being changed, and if yes then sets the timestamp column.

[hansg · Accepted solution]

Thanks!, we will think about it.

[VolkerBarth · Accepted solution]

I guess you could also leave the default as DEFAULT TIMESTAMP and use a before update trigger to set the column to its previous value if all other (relevant) columns are left unchanged, something like

   ...
   referencing old as o new as n
   for each row
   begin
      if     n.col1 is not distinct from o.col1
         and n.col2 is not distinct from o.col2
         and ...
         and n.colX is not distinct from o.colX
      then
         set n.my_timestamp_col = o.my_timestamp_col;
      end if;
   end;

That might be more fitting if you usually expect "real" updates (i.e. those that do modify data).

[MarkCulp · Accepted solution]

I had thought of this solution as an alternative but I'm not sure it will work - the server might set the timestamp column after the trigger is executed. Some testing is required to check if this will work?

[VolkerBarth · Accepted solution]

Well, I agree, and I have not tested that (therefore my usage of "I guess you could..." and "something like").

FWIW, several years ago, Glenn had given some detailed insights (as expected...) on the internal steps a trigger has to consider - cf. that old NNTP article "Before update trigger and computed column " - I'm not sure whether it is still true (well, I'm aware that something relevant has changed with computed columns in v11), however, according to Glenn's statements, normal defaults would be evaluated before any triggers are called. I'm not sure whether DEFAULT TIMESTAMP counts as a normal default.

So yes, some testing might be required:)