Wells Oliver <wells.oliver@xxxxxxxxx> writes: > Had an issue tonight where I had a bunch of stalled queries from a > client connection and I just... could... not... kill... them. We > disconnected the client machine, turned it off, picked it up, shook > it around, yelled at it, and still these idle queries remained in > pg_stat_activity. Idle backends won't respond to a cancel since if they're "idle" there's nothing to cancel :-) If OTOH you tried pg_terminate_backend and this too drew no response, then you might have a case of backend blocked in a critical section such as SendV, symtomatic of a backend trying to write to a full pipe. HTH > > Then I did select pg_cancel_backend(pid) from pg_stat_activity where > client_addr = '..' and they just would... not... go.. away. > > So me being the big smart system administrator guy with shell access, > I logged in, and did a kill -9 xxx where xxx was the sme pid from the > pg_stat_activity result and... they finally went away! > > Felt good about myself until I realized, well, so did every other > connection, and in fact PG momentarily went into recovery mode. > > Everything was fine, but a) why is it a bad idea to kill -9 a client > PG process, but pg_cancel_backend() is OK-- and b) what to do about > stalled PG queries that won't die when you disconnect AND when you > pg_cancel_backend() them? > > Thanks! > > -- > Wells Oliver > wells.oliver@xxxxxxxxx > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800
