Backend killer function

Administrative Snippets

Backend Killer Function

Works with PostgreSQL

7.x – 8.3

Written in

plperlu

Depends on

Nothing

Author: Emanuel Calvo Franco

This function is only useful on 8.3 and earlier; on newer PostgreSQL versions (8.4 and up), you can use the pg_terminate_backend() function.

This function sends a TERM signal to kill the server process indicated in the parameter.

Be aware that all PostgreSQL versions prior to 8.4 are known to have the potential to leave stuck locks or other corruption of shared-memory data structures if backends are killed in this way. In most cases these are low probability events; but if they do occur it will be necessary to restart the postmaster in order to clear them out. (Internally, prior to version 8.4, the postmaster only uses the TERM signal when it is in the process of shutting down anyway.)

create or replace function pg_terminate_backend(int) returns int
language plperlu security definer volatile as $$
        $pid = shift;
	my $myPid = spi_exec_query('select pg_backend_pid();');
	if ($pid ==  $myPid->{rows}[0]->{pg_backend_pid}){
		elog(WARNING, "this PID belongs to the current server process");
		return 1;
	}
	my $othersesions = spi_exec_query('SELECT procpid FROM pg_stat_activity');
	my $numRows = $othersesions->{processed};

	foreach my $rn (0 ..  $numRows - 1) {
        	my $rs = $othersesions->{rows}[$rn];
		if ($rs->{procpid} == $pid){
			$ret = kill TERM, $pid;
			elog WARNING, "could not send signal TERM to $pid: $!" unless $ret == 1;
			return $ret;			
		}
	}
	elog(WARNING, "PID $pid is not a PostgreSQL server process");
	return 1;
$$;

(Note that this function assumes that the stats collector is running.)

How do I find the PID of the session I want to kill?

To find out the PID of the current session, run

select pg_backend_pid();

but of course you cannot kill that one with this function.

This query lists the PIDs of currently active processes, and the SQL commands they are running:

 select procpid, current_query from pg_stat_activity;

 procpid |                    current_query                     
---------+------------------------------------------------------
    2033 | <IDLE>
    2478 | select procpid, current_query from pg_stat_activity;
(2 rows)

You can also try using your operating system's process listing:

$ ps feu -C postmaster -C postgres
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  1402  0.0  0.2  63748  4960 pts/8    S    20:05   0:00 postmaster PGDATA=/pgsql/install/83
postgres  1409  0.0  0.0  63748  1272 ?        Ss   20:05   0:00  \_ postgres: writer process                                
postgres  1410  0.0  0.0  63748  1076 ?        Ss   20:05   0:00  \_ postgres: wal writer process                            
postgres  1411  0.0  0.0  63884  1508 ?        Ss   20:05   0:00  \_ postgres: autovacuum launcher process                   
postgres  1412  0.0  0.0  34240  1196 ?        Ss   20:05   0:00  \_ postgres: stats collector process                       
postgres  2033  0.0  0.2  65236  5768 ?        Ss   20:12   0:00  \_ postgres: postgres postgres [local] idle idle