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

On Thu, March 18, 2010 12:29, Bill Moran wrote:
> 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/
>
> --

Yes, Windows does have Netstat (C:\Windows\System32 under Windows XP).
Use the -b option to determine which program is using the port.

Tim
--
Timothy J. Bruce

Registered Linux User #325725