Cancellation mechanics in PostgreSQL

Interrupting a running statement works through a separate connection: a CancelRequest message is sent with a secret key that the server handed out when the original connection was established. The key matters, because without it any user could cancel anyone's query. libpq exposes PQgetCancel() and PQcancel() for this, other database APIs offer equivalents, psql maps Ctrl+C to a cancel request, and GUI clients typically have a button.

To cancel a statement belonging to another session, call pg_cancel_backend(); pg_terminate_backend() terminates the session itself. Both require superuser rights, membership in pg_signal_backend, or a connection as the same database user whose session you are targeting — you may always cancel your own statements.

Signals and the interrupt flags

When the postmaster receives a CancelRequest, it sends SIGINT to the backend of that session, which is exactly what pg_cancel_backend() does. pg_terminate_backend() sends SIGTERM instead. The signal handler itself does not interrupt execution; it only sets global flags: QueryCancelPending for SIGINT and ProcDiePending for SIGTERM. The backend reacts when convenient, so that it is never stopped mid-operation with shared memory in an inconsistent state.

The reaction happens at calls to the macro CHECK_FOR_INTERRUPTS(), which invoke ProcessInterrupts(). Those calls sit at safe points throughout the code base, and the function either raises the error that aborts the current statement or ends the backend process, depending on which flag was set.

When a cancel has no effect

Three situations leave a query running despite the signal:

  1. The execution path is a loop without any CHECK_FOR_INTERRUPTS() call. That is a PostgreSQL bug, fixed by adding the missing call.
  2. Execution is inside a third-party C function invoked from SQL. The bug belongs to that function's author.
  3. Execution is stuck in a system call that cannot be interrupted, pointing at an OS or hardware problem. Signals are not delivered while a process is in kernel space.

Why kill -9 is dangerous

A plain kill against a backend is harmless: it delivers SIGTERM and is equivalent to pg_terminate_backend() for that session. kill -9 sends SIGKILL, which cannot be caught and stops the process immediately. The postmaster notices that a child did not shut down cleanly and responds by killing all other PostgreSQL processes and running crash recovery, taking the whole database down for seconds to minutes.

Worse still is kill -9 on the postmaster itself: it opens a window in which a new postmaster can start while children of the old one are still alive, a situation that can corrupt data on disk. Never, ever, kill the postmaster process with kill -9!

If even SIGKILL fails, the backend is blocked in an uninterruptible system call — I/O against network attached storage that has disappeared, for instance. Should that persist, only a reboot clears the process.

Avoiding crash recovery with the debugger

In some cases the following procedure on Linux with the GNU debugger lets you drop a stuck backend without triggering recovery. It is not for the faint of heart.

The hanging function

Start from this C source file, loop.c:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

#include 'postgres.h'

#include 'fmgr.h'

#include

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(loop);

Datum loop(PG_FUNCTION_ARGS)

{

/* an endless loop */

while(1)

sleep(2);

}

Build it as a shared library, adjusting the include path:

1

2

gcc -I /usr/pgsql-14/include/server

-fPIC -shared -o loop.so loop.c

Then copy the resulting file into the PostgreSQL shared library directory, obtainable with pg_config --libdir, and define the SQL function as superuser:

1

2

CREATE FUNCTION loop() RETURNS void

   LANGUAGE c AS 'loop';

Reproducing the hang

Any user can now call the function:

1

SELECT loop();

Execution hangs, and a cancel request will not stop it.

Finding the backend and signaling it

Open a second connection as the same database user and look up the process ID that identifies the session:

1

2

3

SELECT pid, query

FROM pg_stat_activity

WHERE query LIKE '%loop%';

Then send that process a SIGTERM:

1

SELECT pg_terminate_backend(12345);

The function returns TRUE because the signal was delivered, but the query keeps running.

Attaching with gdb

Install gdb; debugging symbols for the PostgreSQL server produce a more readable trace but are not required for this trick. On the database server machine, as the PostgreSQL user (usually postgres), attach to the backend using the correct path to the postgres executable and the process ID:

1

gdb /usr/pgsql-14/bin/postgres 12345

At the (gdb) prompt, run bt for a stack trace resembling this:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

#0  __GI___clock_nanosleep (clock_id=clock_id@entry=0, flags=flags@entry=0,

    req=req@entry=0x7ffdaf61cde0, rem=rem@entry=0x7ffdaf61cde0)

    at ../sysdeps/unix/sysv/linux/clock_nanosleep.c:71

#1  0x00007f113d864897 in __GI___nanosleep (req=req@entry=0x7ffdaf61cde0,

    rem=rem@entry=0x7ffdaf61cde0) at ../sysdeps/unix/sysv/linux/nanosleep.c:25

#2  0x00007f113d8647ce in __sleep (seconds=0) at ../sysdeps/posix/sleep.c:55

#3  0x00007f113e623139 in loop () from /usr/pgsql-14/lib/loop.so

#4  0x00000000006d71fb in ExecInterpExpr (state=0x13837b8, econtext=0x13834e0,

    isnull=) at executor/execExprInterp.c:1260

#5  0x000000000070e391 in ExecEvalExprSwitchContext (isNull=0x7ffdaf61ced7,

    econtext=0x13834e0, state=0x13837b8)

    at executor/../../../src/include/executor/executor.h:339

#6  ExecProject (projInfo=0x13837b0)

    at executor/../../../src/include/executor/executor.h:373

#7  ExecResult (pstate=) at executor/nodeResult.c:136

#8  0x00000000006da8b2 in ExecProcNode (node=0x13833d0)

    at executor/../../../src/include/executor/executor.h:257

#9  ExecutePlan (execute_once=, dest=0x137f4c0, direction=,

    numberTuples=0, sendTuples=, operation=CMD_SELECT,

    use_parallel_mode=, planstate=0x13833d0, estate=0x13831a8)

    at executor/execMain.c:1551

[...]

Such a trace is valuable for locating the fault — include it if you report a bug to PostgreSQL. If you would rather not continue, type detach to let the process keep running.

Letting the backend exit cleanly

The trace above shows execution outside PostgreSQL code, inside a custom function (in loop () from /usr/pgsql-14/lib/loop.so), which makes an exit reasonably safe. If the stack instead points into the server itself, there is a small risk that PostgreSQL is midway through modifying shared state or holding a spinlock; reading the call stack with knowledge of the source helps judge that risk. If you accept it, call ProcessInterrupts(); with ProcDiePending set, the process will exit:

1

2

3

4

5

6

(gdb) print ProcessInterrupts()

[Inferior 1 (process 12345) exited with code 01]

The program being debugged exited while in a function called from GDB.

Evaluation of the expression containing the function

(ProcessInterrupts) will be abandoned.

(gdb) quit

Fixing the function

Change the function so the user can cancel it:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

#include 'postgres.h'

#include 'fmgr.h'

#include 'miscadmin.h'

#include

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(loop);

Datum loop(PG_FUNCTION_ARGS)

{

/* an endless loop */

while(1)

{

CHECK_FOR_INTERRUPTS();

sleep(2);

}

}

Calls are now checked for interrupts every two seconds, so execution can be canceled safely.

Summary

Cancels reach the backend as SIGINT, and termination as SIGTERM. When neither works, attaching to the hung backend with gdb and calling ProcessInterrupts() directly makes it exit.

A related way to stop abandoned queries from running forever, and idle sessions from closing, is TCP keepalive.