How do I find out the numeric ID of the current Postgres Transaction?

Question

On psql I can type the following:

BEGIN;

what do I type to get the current id of the newly created transaction?

I am trying to put together a demo that explains how vaccum and MVVC work in postgres. For example select xmin, xmax, * from test; shows the xmin and xmax of each row from the point of view of the current transaction.

I understand the basic theory of it but want to put together an interactive exercise such that I can have two psql consoles open and then have a set of step by step instructions that show mvcc and vaccum work.

How do I get the current postgres transaction id?

Answer 1

Postgres has added txid_current_if_assigned() in version 10.

Answer 2

"System Information Functions", "Transaction IDs and Snapshots".

Answer 3

You can get the transaction id from:

txid_current()

You can additionally get the in-progress transactions in the snaption it's seeing from:

txid_snapshot_xip(txid_current_snapshot())

A few more functions are detailed in the manual:

http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-TXID-SNAPSHOT