CODEWITHSUNDEEP
postgresqltransactionssqlalchemy
Gunnlaugur Briem
Gunnlaugur Briem

Reputation: 2804

How to check for pending operations in a PostgreSQL transaction

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.

Upvotes: 35

Views: 69818

Answers (4)

Chris F Carroll
Chris F Carroll

Reputation: 12370

Since Postgres 10:

select txid_current_if_assigned();

will return null if there is no current transaction.

If a Start Transaction has been issued, it will still return null if there have been no updates.

Upvotes: 5

shaunc
shaunc

Reputation: 5611

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.

Upvotes: 10

Magnus Hagander
Magnus Hagander

Reputation: 25118

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.

Upvotes: 0

Kuberchaun
Kuberchaun

Reputation: 30342

Start by checking into system view pg_locks.

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

Upvotes: 16

Related Questions