Geek Answers Handbook

How to track a transaction that is not yet committed to Postgres for debugging?

Is there a postgres tool that will allow me to track a transaction and run queries as if I were part of this transaction.

I have a breakpoint on my Java code that started a transaction but has not committed a transaction yet because it waits at a breakpoint. What I would like to do is the ability to see the state of the database as the transaction sees it.

Ideally, I would like to use a tool that would allow me to have some kind of sql console where I can type stuff as part of this tx, so I could write select commands to see things that haven't been done yet.

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

+4
source share
2 answers

I don’t know what out of the box means to “browse” or reproduce exactly what is happening, but there are a few hidden columns, functions and directories that can help with your forensics.

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

SELECT xmin, xmax, ... FROM table

, , xid. , = . , :

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

- xmax , ​​ . , .

( ), pageinspect contrib:

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

, , . txid_snapshot ( ) . , - xip_list , , , select , , .

, , :

select * from pg_stat_activity;
select * from pg_locks;

, pg_stat_activity pid.

pg_locks xid pid, , , , .

, , , :

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

- , pgbouncer. Pgbouncer - , db . , pgbouncer, , 1 .. - db. - db pgbouncer - "server_reset_query" pgbouncer , . , , , , .

+1

More articles:


All Articles