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.
2 Answers
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
answered Jun 3, 2020 at 12:03
Panagiotis Kanavos
134k16 gold badges211 silver badges271 bronze badges
1 Comment
Joerg
What are the practical consequences of vacuuming for
xmin when used for optimistic locking/concurrency? In this answer there is a comment that warns about it. The Postgres documentation is somewhat lengthy and complex, talking about FrozenTransactionId and BootstrapTransactionId, which I do not understand. Could you elaborate on what that means in practice, please?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.
8 Comments
Panagiotis Kanavos
That's not the same thing as SQL Server's
rowversion, which is used in optimistic concurrency scenarios. The equivalent in PostgreSQL seems to be xmin, used by NpgSQL for just that scenario@PanagiotisKanavos: if the rowversion is simply an incrementing number, then a value taken from a sequence seems to be exactly the same thing. For which purpose that number is used is a different question. But that could absolutely be used for optimistic locking (I use the table based "version number" quite frequently)
Panagiotis Kanavos
It's not though. It's only meant for optimistic locking. Back in the 2005 days (or even earlier),
timestamp, the depredated name for rowversion was an incrementing number indeed, but that caused too much sync overhead in high-traffic systems, and paralleled operations. Now, each worker can use its own batches of values, resulting in gaps and out-of-order values. Like many, I've tried to use rowversion for syncing and found the hard way there were no guaranteesPanagiotis Kanavos
A database version is used with change tracking, but that's a completely different feature and that value simply isn't used to generate
rowversion. Essentially, it's the change table ID for a change record generated when rows are modified in a table with Change Tracking enabled@PanagiotisKanavos: sorry I don't get it If
rowversion is an incrementing number, then my solution absolutely does this. You can either have one global sequence or an incrementing number per table (that can also be done using a sequence per table, but I don't see the reason why) |
rowversion? That's a specific type used in optimistic concurrency. Npgsql uses xmin for the same jobrowversionis not a global auto ID, it can have gaps or appear to be out of order. The only real guarantee is that for the same row, every modification will change therowversion. For perf reasons, different threads can use batches ofrowversionsto avoid synchronisation across workers.