Best alternate of SQL Server rowversion (IROW_VERSION) in PostgreSQL?

Question

I have a row version column in SQL Server table. At the time of update and insert command auto generate value automatically inserted/updated in this column. I need same in PostgreSQL. One option is to use function trigger with sequence generation but it may cause locking issue. What is the best practice/alternate in PostgreSQL.

Answer 1

The question is somewhat unclear. rowversion in SQL Server is only used as a concurrency token in optimistic concurrency scenarios. This is faster than a trigger that updates a LastModified timestamp or increments a stored column.

The equivalent in PostgreSQL is the system-provided xmin column:

xmin

The identity (transaction ID) of the inserting transaction for this row version. (A row version is an individual state of a row; each update of a row creates a new row version for the same logical row.)

Essentially, for a single row, xmin always changes after every modification, just like rowversion does. It's faster than a trigger too, since it requires no extra effort.

The NpgSQL provider for Entity Framework uses xmin as a concurrency token.

If you want to implement optimistic concurrency manually, read the xmin column in your SELECT statement and use that value in updates, eg:

SELECT xmin, ID, Name FROM sometable;

Which returns

 xmin | ID | name
------+----+------
  123 | 23 | Moo

And then

UPDATE sometable 
SET name = 'Foo' 
WHERE ID = 23 AND xmin = 123

If the row was modified by some other transaction, xmin won't match and no changes will be made. You can detect that by checking how many rows were changed using your provider's API. That's how rowversion works too.

Another possibility mentioned in the linked question is to use the RETURNING clause to return some value to the client. If no value is returned, the statement failed, eg:

UPDATE sometable 
SET name = 'Foo' 
WHERE ID = 23 AND xmin = 123
RETURNING 1

Answer 2

Not sure what "locking issue" you are talking about, but you can get something equivalent (assuming I understood the "row version" thing correctly) without a sequence:

create table some_table
(
  .... columns of the table ..., 
  row_version bigint not null default 0
);

Then create a trigger function:

create function increment_row_version()
  returns trigger
as
$$
begin
  new.row_version := old.row_version + 1;
  return new;
end;
$$
language plpgsql;

By using the old record, it's impossible to overwrite the row version value in an UPDATE statement.

And then create the trigger for every table where you need it:

create trigger update_row_version_trigger
  before update on your_table_name_here
  for each row
  execute procedure increment_row_version();

If you also want to prevent inserting e.g. a higher number as the start number, you can extend the trigger to run on insert and update and in case of an insert assign the desired start value explicitly.

---

If you need a global value across all tables (rather than one number for each table as the above does), create a sequence and use nextval() inside the trigger rather than incrementing the value. And no, the use of a sequence will not cause "locking issues".

The trigger would then look like this:

create function increment_row_version()
  returns trigger
as
$$
begin
  new.row_version := nextval('global_row_version_sequence');
  return new;
end;
$$
language plpgsql;

and can be used for both insert and update triggers.