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

No, not from the database level, really. Perhaps you can add some tracing at the sqlalchemy level to track it?

Also, how do you define a no-op? What if you updated a value to the same value it had before, is that a no-op or not? From the databases perspective, if it had one, it would not be a no-op. But from the application perspective, it probably would.