On Mon, Dec 6, 2010 at 4:31 PM, felix <crucialfelix@xxxxxxxxx> wrote: > > thanks for the replies !, > but actually I did figure out how to kill it > but pb_cancel_backend didn't work. here's some notes: > this has been hung for 5 days: > ns | 32681 | nssql | <IDLE> in transaction | f | 2010-12-01 > 15 Right, pg_cancel_backend() isn't going to help when the session you're trying to kill is '<IDLE> in transaction' -- there's no query to be killed. If this '<IDLE> in transaction' session was causing problems by blocking other transactions, you should look at the application running these queries and figure out why it's hanging out in this state. Staying like that for 5 days is not a good sign, and can cause also problems with e.g. autovacuum. [snip] > but it still will not die > the docs for pg_ctl state: > "Use pb_ctl --help to see a list of supported signal names." > doing so does indeed tell me the names: > HUP INT QUIT ABRT TERM USR1 USR2 > but nothing about them whatseover :) I agree this could be better documented. There's a brief mention at: http://www.postgresql.org/docs/current/static/app-postgres.html#AEN77350 "To cancel a running query, send the SIGINT signal to the process running that command." though that snippet of information is out-of-place on a page about the postmaster, and SIGINT vs. SIGTERM for individual backends isn't discussed there at any rate. At any rate, as you discovered, you have to send SIGTERM to the backend to kill off an '<IDLE> in transaction' session. If you're using 8.4 or newer, you have pg_terminate_backend() as a SQL wrapper for SIGTERM. If you're using an older version, be careful, see e.g. http://archives.postgresql.org/pgsql-admin/2010-04/msg00274.php Josh -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
