Cancel/Termination Examples

 One of the small little treats provided in PostgreSQL 8.4 is the new pg_terminate_backend function. In the past when we wanted to kill runaway postgresql queries issued by a database or user or hmm us, we would call the pg_cancel_backend function. The problem with that is it would simply cancel the query in the backend process, but often times the offending application would simply launch the same query again.

In PostgreSQL 8.4 a new function was introduced called pg_terminate_backend. This doesn't completely replace pg_cancel_backend, but basically does what you do when you go into say a Windows Task manager and kill the offending postgres process or on Linux, you call a kill command on a postgres process. Its nicer in the sense that you can do it all within PostgreSQL and you can use the pg_stat_activity query to help you out a bit. Also you don't run the risk as easily of killing the root postgres process and killing the postgres service all together.

Below are the ways we commonly use these functions. One of the features I really love about PostgreSQL which I miss when working with SQL Server, is the ability to call a function that does something from within a query. This feature makes SQL so much more powerful.

Cancel/Termination Examples

   -- Cancel all queries in an annoying database

SELECT pg_cancel_backend(procpid)

FROM pg_stat_activity

WHERE datname = 'baddatabase';

-- Cancel all queries by an annoying user

SELECT pg_cancel_backend(procpid)

FROM pg_stat_activity

WHERE usename = 'baduser';

Baseball bat to the head

Terminating backends is also useful for freeing up memory from idle postgres processes that for whatever reason were not released or if you need to rename a database and need to kill all live connections to a database to do so.

-- terminate process by annoying database

SELECT pg_terminate_backend(procpid)

FROM pg_stat_activity

WHERE datname = 'baddatabase';

-- terminate process by an annoying user

SELECT pg_terminate_backend(procpid)

FROM pg_stat_activity

WHERE usename = 'baduser';

One thing we have noticed with the baseball approach to database management is that it doesn't always play well with pooled connection like things. For example in one PHP app we had that uses pooled connections, the connections became stale. Or at least we think this is the culprit. So you sometimes have to restart the app, or it sometimes gives annoying messages to the user until the dead connections are released. With that said, we still try the slap on the wrist before reaching for the baseball bat. Its always nice to have an easy to swing baseball bat handy though.