Recently, during a training, a colleague asked me how to find out the current TransactionID in a Greenplum Database system. This piece of information is important in order to find out if a VACUUM run is required on a table.
In PostgreSQL (Greenplum Database is a PostgreSQL fork) this is easy:
Current TransactionID in PostgreSQL:
postgres=> SELECT txid_current(); txid_current -------------- 816 (1 row)
Unfortunately, the merge process with PostgreSQL has not yet merged in this function. Therefore things get a bit more complicated, and a workaround is required. The system table ‘pg_locks’ shows, among other information, the TransactionID. Selecting from this table will show at least two records: one AccessShareLock on ‘pg_locks’ itself, and one ExclusiveLock on the current TransactionID.
Current TransactionID in Greenplum:
postgres=> SELECT transactionid FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'transactionid' AND mode = 'ExclusiveLock' AND granted = 't'; transactionid --------------- 1325 (1 row)
In most cases it is enough to just limit the results by ‘pid’ and ‘locktype’.
Current TransactionID in Greenplum (shorter):
postgres=> SELECT transactionid FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'transactionid'; transactionid --------------- 1325 (1 row)
On the downside, this query is consuming yet another TransactionID, if run in a separate transaction.