PostgreSQL 10: Trace the Status of Transaction (Commit, In Progress, Rollback, Aborted)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

PostgreSQL 10 introduced many useful features that we can use in day to day work.

Now, we can trace the status of the running transaction in PostgreSQL 10. If we have transaction IDs of our sessions and suddenly database crashed or shutdown, we can check the status of those transactions.

We can check the status like Committed, In Progress, Rollback or Aborted. This facility will help a lot for taking a necessary decision on crashed transactions.

PostgreSQL 10 introduced two functions:

txid_current() – will return the current transaction id of a session
txid_status() – will return the status of transaction

Please check the below sample:

Create a sample table:

Open a transaction, and insert a record:

Check the current transaction id:

Check the status of above transaction id:
It is showing “in progress” because I didn’t commit the transaction yet.

Now, execute the COMMIT:

Now, check the status again:

You can also return transaction id, with the insert statement: (Using RETURNING)