How to check for pending operations in a PostgreSQL transaction

Question

I have a session (SQLAlchemy) on PostgreSQL, with an active uncommitted transaction. I have just passed the session to some call tree that may or may not have issued SQL INSERT/UPDATE/DELETE statements, through sqlalchemy.orm or directly through the underlying connection.

Is there a way to check whether there are any pending data-modifying statements in this transaction? I.e. whether commit would be a no-op or not, and whether rollback would discard something or not?

I've seen people point out v$transaction in Oracle for the same thing (see this SO question). I'm looking for something similar to use on PostgreSQL.

Answer 1

Start by checking into system view pg_locks.

http://www.postgresql.org/docs/8.4/interactive/view-pg-locks.html

Answer 2

Consider the following sequence of statements:

select txid_current();

begin;

select txid_current();

If the transaction id returned by the two selects is equal, then there is an open transaction. If not then there wasn't, (but now is).

If the numbers are different, then as a side effect you will just have opened a transaction, which you will probably want to close.

UPDATE: In fact, as @r2evans points out (thanks for the insight!), you don't need the "begin" -- txid_current() will return the same number just if you are in a transaction.