[Postgres] Identifier les attentes

1. Find the pid

PostgreSQL creates one process per connection, and it identifies each process with its operating system process ID, or pid. In order to cancel a query, you need to know the pid for the connection it’s running on.

One way to find this out is with the pg_stat_activity view, which provides information about the live queries. For example, try this query:

SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;

The substr call limits the displayed SQL to 100 characters, to avoid long queries taking up a lot of screen space. The backend_type filter avoids showing background server processes, such as the autovacuum launcher. Sorting by backend_start shows the longest running connections first, which often shows problematic long-running transactions.

2. Terminate, or Cancel, the Process

PostgreSQL has two query-stopping functions, with differences discussed below.

The “harsh” one is pg_terminate_backend, which you can use like:

SELECT pg_terminate_backend(pid);

I default to using this one, reasoning explained below.

The “kinder” function is pg_cancel_backend, which you can use like:

SELECT pg_cancel_backend(pid);

When using either, replace pid with the pid you found in step 1. For example, to terminate the long-running query from above:

SELECT pg_terminate_backend(77);

And boom, it’s gone.

There are two differences that make pg_terminate_backend “harsher”.

First, pg_terminate_backend completely stops the process, causing the connection to close. This rolls back any open transaction on the connection, freeing all locks it holds.

In contrast, pg_cancel_backend interrupts only the running query, leaving the connection open. The current transaction or savepoint is aborted. Thus, if the connection is using savepoints, it can still keep the surrounding transaction open, with pending data changes and locks.

Second, pg_terminate_backend applies immediately*, whilst pg_cancel_backend can be deferred at certain points in the backend process lifecycle. So sometimes, you might run pg_cancel_backend and see nothing happen for a while. Specifically, this can occur when the process is reading input from the client, such as an incoming query. The backend process defers handling the cancellation until all the input has been read, since otherwise the connection could not be left open and functional.

Queries Blocking a Particular Process

If you’re executing an ALTER TABLE and find it blocked, waiting for a table lock, you may want to terminate the connections holding locks on that table. This will allow the ALTER TABLE to proceed.

For example, I recently worked on an application with a few long-running transactions that blocked a database migration. These long-running queries were safe to terminate as the responsible application process would re-run later and fill in any gaps.

You can find the blocked pid of ALTER TABLE with a query against pg_stat_activity like so:

SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND query LIKE 'ALTER TABLE%'
ORDER BY backend_start;

With the blocked pid, you can use this query with pg_blocking_pids to generate SQL to terminate the blocking processes:

SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(blockedpid));

To run this query, replace blockedpid with the pid of the blocked process. Then copy-paste the output lines and run them.

SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE backend_type = 'client backend'
      AND pid != pg_backend_pid()
      AND backend_start < NOW() - '10 seconds'::interval;

 Find a query with his pid.

SELECT substr(query, 0, 5000) q
FROM pg_stat_activity
WHERE pid= '<pid>';