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

Question

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;

Answer 1

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.

Answer 2

From PostgreSQL version 10 and onward (i.e., all supported versions as of May, 2022), there is, besides the txid_current() function already mentioned by @Gajus, also a txid_current_if_assigned() function, which returns “NULL if no [transaction] ID is assigned yet” allowing you “to avoid unnecessary consumption of an XID” (which is the problem that @Gajus was hinting at in his answer from 2018).

In fact, since PostgreSQL 13, these txid_-prefixed functions are deprecated in favor of pg_current_xact_id() and pg_current_xact_id_if_assigned().

The reason that, if you just want to find out if you are currently inside of a transaction, it's better to use pg_current_xact_id_if_assigned() than pg_current_xact_id() is because, as a rule of thumb, you want to avoid unnecessary XID consumption. GitLab, for instance, ran into problems because they used save points too extensively.

However, a caveat also applies to pg_current_xact_id_if_assigned(). It will return NULL as long as nothing has been written within your transaction, because PostgreSQL doesn't assign a transaction ID until a write operation occurs. A possible workaround is to create a temporary table that is bound the transaction:

SELECT pg_current_xact_id_if_assigned() IS NOT NULL AS is_transaction;

 is_transaction
----------------
 f
(1 row)

BEGIN TRANSACTION;  -- Making it explicitly READ WRITE won't make a difference.

SELECT pg_current_xact_id_if_assigned() IS NOT NULL AS is_transaction;

 is_transaction
----------------
 f
(1 row)

CREATE TEMPORARY TABLE a (b int) ON COMMIT DROP;

SELECT pg_current_xact_id_if_assigned() IS NOT NULL AS is_transaction;

 is_transaction
----------------
 t
(1 row)

There you have it.

If you where to run the CREATE TEMPORARY TABLE … ON COMMIT DROP statement outside of a transaction, it will have disappeared by the end of the implicit transaction that each mutating statement (in auto-commit-mode) is executed in.

Edit 1: Do note that you cannot wrap this logic into a function or procedure, because the function/procedure call statement will be wrapped in an implicit transaction, and thus your am_i_inside_a_transaction() function would always return TRUE.

Edit 2: I want to make it clear that, besides the informational value of my answer, I do as of yet believe the comparing timestamps method from @Gajus his own answer to be superior to the pg_current_xact_id_if_assigned() method. And his method can be made even more explicit by using transaction_timestamp() instead of now():

CREATE FUNCTION in_transaction() RETURNS boolean
LANGUAGE SQL
RETURN transaction_timestamp() != statement_timestamp()
;

SELECT in_transaction();

 in_transaction
----------------
 f
(1 row)

BEGIN;
SELECT in_transaction();

 in_transaction
----------------
 t
(1 row)

A caveat applies here too, though, since statement_timestamp() is “the time of receipt of the latest command message from the client”. That means that if you are to nest the above function call in another function, the function will always return FALSE.

Answer 3

Funny enough, psql and executing the same statement from within an application yields different results. I tried a Tcl script and DBeaver.

Statement: SELECT now(), statement_timestamp();

psql: 2021-05-07 13:31:10.135851+02 | 2021-05-07 13:31:10.135851+02

DBeaver: 2021-05-07 13:31:00.633784+02|2021-05-07 13:31:00.633913+02

Tcl script: {now {2021-05-07 13:24:54.659837+02} statement_timestamp {2021-05-07 13:24:54.65985+02}}

So in psql now() and statement_timestamp() are identical when not in a transaction, while in the others the two values vary a little bit, roughly 100 microseconds.