9

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;
0

3 Answers 3

12

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.

3
  • 1
    +1 In Postgres 14 you can use pg_current_xact_id_if_assigned() to avoid/delay the downside of consuming xid values.
    – Eric Mutta
    Jul 26, 2022 at 15:09
  • 1
    This doesn't work for me. BEGIN; SELECT now() = statement_timestamp(); returns TRUE if submitted in a single batch.
    – polyglot
    Sep 19, 2022 at 3:42
  • Same for me - this method only works if the BEGIN and the SELECT are run in separate batches.
    – EM0
    Oct 11, 2022 at 16:28
7

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.

0

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.

3
  • 2
    I don't think this actually answers the question. If you want to know what causes such behaviour, you should probably ask a separate question.
    – mustaccio
    May 7, 2021 at 12:20
  • 1
    It doesn't, but provides a helpful warning that the previous answer may not always work. Jan 14, 2022 at 13:57
  • @HolgerJakobs, Are you sure that DBeaver and the Tcl Postgres library do not wrap your statements within a transaction with the settings you used? I'v never use DBeaver, but their docs suggest that, if you were running in “Manual commit” mode, it will precede your statements with a START TRANSACTION statement.
    – BigSmoke
    May 5, 2022 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.