How to spy on a transaction that is not yet committed in Postgres for debugging?

Question

Is there a postgres tool that would allow me to spy on a transaction and run queries as if as I was part of that transaction.

I have a break point on my java code which has started a transaction but has not yet committed the transaction because it is waiting at a break point. What I would like to do is to able to see the state of the database the way that the transaction sees it.

Ideally I would like a tool that would let me have a sql console of some kind where I can type stuff as part of that tx, so I could write select statements to see things that have not yet been committed.

I only need such a tool for my development workstation, I am using postgres 9.1 but can easily upgrade to 9.2 or 9.3 if needed.

Answer 1

I'm not aware of any out of the box means to "view" or replay what is going on exactly, but there are a few hidden columns, functions and catalogs that may be of help in your forensics.

The key system columns of interest to you are xmin and xmax. You can query them like so:

SELECT xmin, xmax, ... FROM table

As an aside, be wary that their type is xid. That's a very capricious version of integer that only responds to = as an operator. Also, it can only be cast to text and back:

select 1::text::xid::text::int, not(1::text::xid = 2::text::xid);

From outside of the offending transaction, xmax would hold the offending transaction's id if the row was updated or deleted by the latter. I'm unaware of any way to see newly inserted rows.

To peek at new rows (though with very little useful information), you can use the pageinspect contrib:

SELECT * FROM heap_page_items(get_raw_page('table', 0));

There are some potentially interesting system information functions to understand what the latter do. They return and make use of a txid_snapshot (an equally capricious type) for use with those columns. I'm afraid I'm unaware of any means to get the precise xip_list of an in-progress transaction without being within it, let alone to masquerade as it when running select statements to view the database as it does, however.

Lastly, there are a view and a table of interest:

select * from pg_stat_activity;
select * from pg_locks;

The first, pg_stat_activity, will give you the last query for your transaction, along with its pid.

That second, pg_locks, will give you the offending xid given a pid, along with the various locks that it acquired on other tables and table rows, so you know where to begin to look.

You could then check out what got touched by running queries such as:

select * from table where xmax = 123::text::xid;