On Thu, March 18, 2010 12:29, Bill Moran wrote: > In response to "Abraham, Danny" <danny_abraham@xxxxxxx>: > >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
