Auto increment field on update I have a field called: update_count, which I need to reflect the number of times a row has been updated.
I have been looking into the SERIAL type for the column which sounds promising, but it has one problem - it is not transaction safe, which means that it may get out of sync. This may not happen.
My question is then: &quot;How to handle an auto incrementing field on each column while supporting multiple concurrent client?&quot;
Environment: Spark.
Auto-increment (aka &quot;identity&quot;) columns are something completely different than &quot;a column indicating the number of updates to a row&quot;.
Even if a sequence (the &quot;magic&quot; behind identity columns) did work without gaps, you still have a single sequence for the entire table, not for each row.
If you want to track how often a row was updated, create a trigger:
create or replace function increment_update_count()
  returns trigger
as
$$
begin
  new.update_count := new.update_count + 1;
  return new;
end;
$$
language plpgsql;

And create the corresponding trigger:
create trigger trigger_track_upate_count
  BEFORE update on the_table --&lt;&lt; change your table name here
  for each row
  execute procedure increment_update_count();