CODEWITHSUNDEEP
sql-serverpostgresqlstored-procedures
Joseph Katzman
Joseph Katzman

Reputation: 2085

Is there PostgreSQL analogue of CHANGE_TRACKING_CURRENT_VERSION (T-SQL)?

I'm trying to convert some SQL Server stored procedure in a PostgreSQL procedure, but I didn't find PostgreSQL analogue of CHANGE_TRACKING_CURRENT_VERSION (T-SQL). Is it possible to retrieve a value that is associated with the last committed transaction? I will use this value as a synchronization parameter. I will pass this synchronization parameter and table name to the procedure and will compare it with a current change tracking value. If they are different it means that the table was modified and I can do something with modified rows.

Upvotes: 0

Views: 349

Answers (1)

Joseph Katzman
Joseph Katzman

Reputation: 2085

After my investigation I would say that we can try to use pg_last_committed_xact. It returns xid and timestamp. We don't need the timestamp, but we can use xid as an analogue of CHANGE_TRACKING_CURRENT_VERSION (T-SQL). Both of the functions return a new value after DML operations.

https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-COMMIT-TIMESTAMP

enter image description here

UPDATE: I was wrong. We can't use pg_last_committed_xact as an equivalent of CHANGE_TRACKING_CURRENT_VERSION.

The pg_last_committed_xact value will be incremented after each successful transaction, while the CHANGE_TRACKING_CURRENT_VERSION value will change only when DML operations are applied to tables for which change tracking is enabled.

Upvotes: 2

Related Questions