Re: How to detect if in transaction?

>> How can I tell if a session is in a pending transaction? In other words, a "BEGIN TRANSACTION" has been issued but the transaction 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 Postgres defaults to AUTO-COMMIT until I issue a "BEGIN TRANSACTION". I want to programmatically detect that condition for the 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 communication protocol, 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, another txn 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 itself without 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 interactive SQL 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 txn is in progress. I've not yet tried that programming.

--Basil Bourque