Re: Strange locking problem

On Tue, May 21, 2013 at 12:24 PM, Moshe Jacobson <moshe@neadwerx.com> wrote:
> I wanted to see what was happening in that transaction ID that might be
> causing this lock to be held, but I was unsure how. I know I can look in
> pg_locks for the other transaction, but that will not tell me what statement
> is executing in that transaction. pg_stat_activity does not have a
> transaction ID column.
>
> How can I see what statement is executing in a transaction?

You can join pg_locks and pg_stat_activity by pid (or procpid = pid if
your version <9.2).

SELECT
    ...
    current_query
    ...
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a ON
    -- procpid = pid -- <9.2
    a.pid = l.pid -- >=9.2
...

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com