Re: stopping processes, preventing connections - Mailing list pgsql-general

 

Second, and the more complicated one - what do I do about rogue queries that are running when my process starts? Today we had a query that ran since yesterday. I called pg_cancel_backend() on it several times and waited for almost two hours - to no avail. Eventually I had to ask our sysadmin to shutdown PostgreSQL, which took some five minutes, but eventually worked. Is there a way to do the same thing to a single process without shutting down the whole server, and without causing any harm to the database or memory corruption? Something I can call from within SQL? I run the nightly script from a linux user which is not "postgres", so I'd prefer a way that doesn't require using "kill".

On 8.4, you can use pg_terminate_backend(), which sends a SIGTERM instead of pg_cancel_backend's SIGINT. If you're not on 8.4, you can just do this manually with a "kill -SIGTERM backend_pid". If that doesn't work either, you might have to resort to a "kill -SIGKILL backend_pid". Killing a single backend should be much better for you than restarting Postgres entirely. These operations shouldn't result in database corruption.

You have to be database superuser to use pg_cancel_backend() or pg_terminate_backend(), or have a shell login as the database user to use "kill". No way around that for now.

Unfortunately, we only have 8.3 - is SIGTERM safe on 8.3?

I guess I'll have to sudo or use local ssh.

Though next time you see a query which doesn't respond to pg_cancel_backend(), try gathering information about the query and what the backend is doing; either you're doing something unusual (e.g. an app is restarting the query automatically after getting canceled) or perhaps you've stumbled on a bug in Postgres.

I'd appreciate it if you tell me what to look for. It was running a join on several tables, but nothing too complicated. It may be that the query is not optimized (one of the tables is not indexed properly) but it still should respond to cancel - shouldn't it?