30 October 2017
One of the essentials of any database system is that a transaction is either in progress, committed, or rolled back. But consider what happens if…
BEGIN; UPDATE table SET money=money+100000; COMMIT; -- And you get an error that the server has disconnected
Did that transaction commit, or didn’t it? You don’t really know, because you don’t know if the server even received the
COMMIT statement; if it didn’t, the transaction might have aborted due to the client disconnection.
This isn’t that important for the vast majority of situations, but if you are (for example) building a two-phase commit architecture, it might be vital to know if the transaction really did commit or not.
In PostgreSQL, you can find this out using
BEGIN; UPDATE table SET money=money+100000; SELECT txid_current(); txid_current -------------- 8168 (1 row) COMMIT; -- And you get an error that the server has disconnected
Now, you can use that result to test the status of that transaction (as long as it is still available in the commit log). So, if a disconnection occurs, the client can reconnect and:
SELECT txid_status(8168); txid_status ------------- aborted (1 row)
Now, the client knows it needs to rerun that transaction.
Again, most client applications don’t need this level of detail, but it’s great to have the capability if you do.