I am running
select * from pg_lock;
to get transactions with exclusive locks.
Once I know the transaction id that has exclusive lock, how can I find out sql query that is associated with that transaction.
select * from pg_stat_activity;
gives me sql queries but not transaction id.
Anybody can help me here?
You need to join on the pid.
-- For PostgreSQL 9.1
select l.pid, l.mode, sa.procpid, sa.current_query
from pg_locks l
inner join pg_stat_activity sa
on l.pid = sa.procpid
where l.mode like '%xclusive%';
-- For PostgreSQL 9.2
select l.pid, l.mode, sa.pid, sa.query
from pg_locks l
inner join pg_stat_activity sa
on l.pid = sa.pid
where l.mode like '%xclusive%';
The pid column can be joined to the pid column of the pg_stat_activity view to get more information on the session holding or waiting to hold each lock. Also, if you are using prepared transactions, the transaction column can be joined to the transaction column of the pg_prepared_xacts view to get more information on prepared transactions that hold locks. (A prepared transaction can never be waiting for a lock, but it continues to hold the locks it acquired while running.)
The columns available and the column names differ slightly between versions 9.1 and 9.2.
select * from pg_locks;. The finalsis important.