3

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

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.

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

1
  • 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 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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