How to check if the current connection is in a transaction?

I need my program code to ensure that certain part of logic is being executed within a transaction.

What query would tell me the current transaction ID / other information that would allow me to determine if I am in a transaction from within the transaction?

BEGIN;
-- How to check if I am in a transaction?
COMMIT;

A simple way is to compare now() to statement_timestamp().

• now() gives the current date and time (start of current transaction).

• statement_timestamp() gives the current date and time (start of current statement).

Example:

SELECT now() = statement_timestamp();
-- TRUE

BEGIN;
SELECT now() = statement_timestamp();
-- FALSE

The other alternative is to execute two queries:

SELECT txid_current();
SELECT txid_current();

and compare the resulting xid. If it is the same, then you are in a transaction.

The downside to the latter approach is that every txid_current() increments xid value and will further advance you to a wraparound.

StackExchange Database Administrators Q#208363, answer score: 11