2

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;
  • Can't you structure your code so that your transaction boundaries are obvious? – Colin 't Hart May 31 '18 at 14:32
2

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.

  • I am running on Google Cloud SQL Postgres and I am able to more or less consistently reproduce SELECT now() = statement_timestamp(); returning TRUE when not in a transaction. – Gajus May 31 '18 at 15:49
  • Looks like you are right. My test was flawed. I have removed the comment about microseconds. – Gajus May 31 '18 at 16:09

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.