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
Slap on Wrist
-- 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.
PostGIS in Action
About the Authors
Consulting
