What is the equivalent of timestamp/rowversion (SQL Server) with PostgreSQL

Question

Could someone help me find the equivalent of Timestamp/Rowversion (SQL Server) with PostgreSQL?

I'm using NHibernate on Mono (UNIX).

I think that Timestamp (PostgreSQL) is the equivalent of Datetime (SQL Server) - it's not what I'm looking for.

Edit 1: For those who don't know what is a Timestamp/Rowversion in SQL Server: http://msdn.microsoft.com/en-us/library/ms182776.aspx (It is mostly used for optimistic concurrency)

Hi W3Max. You may want to take a look at http://stackoverflow.com/questions/1035980/postgresql-update-timestamp-when-row-is-updated (although it's not specific to NHibernate). — Matt Solnit, Nov 14 '09 at 19:01
It would help if you'd say what is "Timestamp/Rowversion" - I, for one, have absolutely no idea if these are datatypes (what range/precision then?) or some specific "functions" (like rownum in Oracle), or magical stuff that makes your eyebrows to grow. — , Nov 15 '09 at 13:08
The OP's rowversion is a value that is "stamped" onto the row automatically by the database engine whenever the row changes. One can use this value to implement optimistic concurrency -- you read the row in, and when you update the row, your update command has a where-condition: where rowversion = theRowVersionReadEarlier. Only if the row has not changed since you read it in will the update take effect. — Tim, Jan 23 '13 at 17:19
Yea - timestamp in MS SQL Server is not a timestamp at all! The mind boggles. No wonder it's been [deprecated](https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#:~:text=The%20timestamp%20syntax%20is%20deprecated). If only MS could "stamp" out the type of thinking from their organisation that got that into their product in the first place, I'm sure a lot of other problems would also disappear. — Reversed Engineer, Oct 13 '21 at 10:18

score 10 · Answer 1 · answered Nov 16 '09 at 08:58

10

See the system columns section in PostgreSQL documentation. In particular, xmin might be what you're looking for.

But be careful using any system columns. They are by definition implementation specific and the details of some of them might change with future versions.

answered Nov 16 '09 at 08:58

Ants Aasma

53,288
15
90
97

xmin can be the same for multiple records if they were inserted in one transaction for example. – mtomy Oct 30 '14 at 11:02
1

`xmin` would be great except it's 32-bit and subject to [vacuuming](http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html) (read non-permanent). – dev Feb 05 '15 at 16:54
@dev what are the consequences of vacuuming for `xmin` when used for optimistic locking? Could you elaborate on this, please? As far as I know, npgsql is using `xmin` for optimistic locking, see https://www.npgsql.org/efcore/modeling/concurrency.html. – Joerg Jun 03 '21 at 08:22
@dev @Joerg this is confusing to me too. I did some research, and the closest [reference](https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/) to non-permanence that I've found refers to dead tuples (with their associated `xmin` values) being removed on vacuum. Freezing seems to set `relfrozenxid` but current and subsequent `xmin` values appear to be consistent. The biggest concerns to me seem to be the tight coupling to the transaction management system as well as possible collisions due to Transaction ID Wraparound. – Mike Hill Jun 15 '21 at 01:29
1

@dev may have been referring to the behavior in versions of Postgres before 9.4, where the `xmin` value would be overwritten by `FrozenTransactionId` during `VACUUM FREEZE`. This appears to no longer be the case as of version 9.4. See the "Note" section here: https://www.postgresql.org/docs/9.4/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND – Mike Hill Jun 15 '21 at 01:32

score 0 · Answer 2 · answered Dec 07 '15 at 03:44

0

You can create it manually:

Create sequence
Create trigger for insert and update for each table that contains rowversion column
Set rowversion column from sequence

answered Dec 07 '15 at 03:44

Sergei Shvets

1,676
1
14
12

score 0 · Answer 3 · edited Apr 15 '19 at 08:41

The key thing being missed is that rowversion type changed each time there is an update to the row, even if the net result is that, the row is not changed.

CREATE TABLE MyTest (myKey int PRIMARY KEY  
    ,myValue int, RV rowversion);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);  
GO  

select * from MyTest
Go

update MyTest set myValue = 0 
go

/* row version will have changed */
select * from MyTest
Go

The only choice I have found is to create an update trigger.

score -5 · Accepted Answer · edited Nov 16 '09 at 10:29

-5

If I understand correctly rowversion is the same as serial? If not - you'd have to make your own with triggers.

edited Nov 16 '09 at 10:29

Stephen Denne

36,219
10
45
60

answered Nov 15 '09 at 19:59

MSSQL provides function MIN_ACTIVE_ROWVERSION to work with rowversion (http://msdn.microsoft.com/en-us/library/bb839514.aspx) that is crucial for implementing synchronization-related systems. It seems Postgres does not. – mtomy Oct 30 '14 at 07:20
@mtomy it's not, in fact `rowversion` *can't* be used for syncing at all. The only guarantee `rowversion` gives is that for a *single* row, any changes will modify it. The system is free to generate those numbers as it sees fit, in batches, with gaps, with different cores using a different pool of batched numbers, resulting in seemingly out-of-order values. Syncing versions are provided by change tracking functions, provided change tracking is enabled for a table – Panagiotis Kanavos Jun 03 '20 at 11:27

What is the equivalent of timestamp/rowversion (SQL Server) with PostgreSQL

4 Answers4

Linked