>> How can I tell if a session is in a pending transaction? In other words, a "BEGIN TRANSACTION" has been issued but
thetransaction has not yet done a COMMIT or ROLLBACK. How can I ask if that is the condition?
>>
>> I've googled and searched the archives. Others have asked the question, but I've not seen an answer. I did read that
Postgresdefaults to AUTO-COMMIT until I issue a "BEGIN TRANSACTION". I want to programmatically detect that condition
forthe sake of defensive programming, logging, debugging, and so on.
>
> What client interface are you using to connect to Postgres? This information is available using the low level
communicationprotocol, but I'm not sure there is an SQL command to give the status.
I suppose I really have two questions:
• How to detect if in "AUTOCOMMIT" mode.
• How to detect if in a transaction (BEGIN issued, waiting for a ROLLBACK or COMMIT).
Or are these two questions flip-sides of the same coin? Is it that in Postgres when your are in AUTOCOMMIT mode you are
*not*in a txn? And vice-versa, if in a txn you cannot be in AUTOCOMMIT mode? If you use turn off the AUTOCOMMIT
variable,is it that you are always in a txn without need for a BEGIN -- every time you issue a ROLLBACK or COMMIT,
anothertxn instantly begins?
I'm using the driver bundled with REAL Studio 2010. It lacks a command to detect either of the situations listed above.
I looked at the source code for the JDBC driver. Turns out "AUTOCOMMIT" status is stored internally in the JDBC driver
itselfwithout actually communicating or coordinating with the Postgres server.
http://jdbc.postgresql.org/
As for detecting if in a transaction, I may have stumbled upon a workaround. While experimenting in pgAdmin's
interactiveSQL window, I found that issuing a second "BEGIN" causes a warning:
---
WARNING: there is already a transaction in progress
---
Perhaps issuing a "BEGIN" through the database driver and then detecting the warning would work as a way to detect if a
txnis in progress. I've not yet tried that programming.
--Basil Bourque
