Re: Who is locking me? - Mailing list pgsql-general

In response to "Abraham, Danny" <danny_abraham@bmc.com>:

> Hi,
>
>
>
> A process hangs forever.
>
> When using this query...
>
>
>
> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
>
>        pg_stat_get_backend_activity(s.backendid) AS current_query
>
>     FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s where pg_stat_get_backend_activity(s.backendid) not
like'<insuff%' 
>
> and  pg_stat_get_backend_activity(s.backendid) not like '<IDLE>';
>
>
>
>
>
> I get...
>
>
>
>
>
>  procpid |                            current_query
>
> ---------+--------------------------------------------------------------
>
> ---------+-------
>
>    26702 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
>
>          :        pg_stat_get_backend_activity(s.backendid) AS current_query
>
>          :     FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s
>
>          : where pg_stat_get_backend_activity(s.backendid) not like '<insuff%'
>
>          : and  pg_stat_get_backend_activity(s.backendid) not like '<IDLE>';
>
>    21282 | update cmr_lastno set lastisn=lastisn+1 where tablename='DANNY';
>
>    25936 | <IDLE> in transaction  <== This is the locker
>
>
>
> And the question:
>
> - What exactly is the locker doing?

Idle in transaction means they aren't doing anything, but have not yet
committed or rolled back the transaction, thus any locks they took out
early in the transaction are still held.

Keep in mind that they aren't doing anything at that instant.  It's possible
that the connection is still working, but only issuing queries every so
often, because it's processing the resultant data.  (It's also possible
that they really aren't doing anything and should be swatted for leaving
the transaction open).

> - Can I retrieve the Client PID somewhere (then by process name I will dive into the code).

If you're on a POSIX system, you can use netstat to find out the details of
the socket the backend is connected to.  If it's a local connection, another
look at netstat will give you the pid of the client.  If it's a remote
connection, then you'll have the client IP and port #.  Depending on your
setup, the client IP alone might tell you what you need to know.  If that's
not enough, you should be able to use the client port # on the client's
system to track down what process is on the client end (again, using
netstat)

If you're not on a POSIX system, you can probably still do what I described,
I just don't know the details of how it's done.  Windows has a netstat
equivalent, I think.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/