Re: Update problem on large table

On Mon, Dec 6, 2010 at 4:31 PM, felix <crucialfelix(at)gmail(dot)com> 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