How do I end long-running queries in my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance?

Short description

Depending on your use case, you can use either the pg_cacnel_backend(pid) or pg_terminate_backend(pid) function.

Use the pg_cancel_backend(pid) function to send a SIGINT signal to a specific backend process and cancel the current long-running query. During this process, the connection to the database remains active. The backend can continue to process other queries or transactions after the function gracefully terminates the current query.

Use the pg_terminate_backend(pid) function to end a query and close the connection. Use this function to send a SIGTERM signal to a specific backend process and forcefully terminate the connection that's associated with the process. This process rolls back and releases open transactions or held locks within the connection.

For more information, see Server signaling functions on the PostgreSQL website.

Note: Some Aurora PostgreSQL-Compatible versions can't end an autovacuum process, even when you meet all system requirements. When you try to end an autovacuum process in these versions, you might receive the following error message:

"ERROR: 42501: must be a superuser to terminate superuser process LOCATION: pg_terminate_backend, signalfuncs.c:227."

Some minor versions allow the rds_superuser to end autovacuum processes that aren't explicitly associated with a role. To check whether your version allows the rds_superuser to end autovacuum processes, see Amazon Aurora PostgreSQL updates.

Resolution

To use pg_cacnel_backend(pid) or pg_terminate_backend(pid), you must be one of the following users:

• You're an rds_superuser or a member of the default role pg_signal_backend.
• You're connected to the database as the same database user of the session that you want to cancel.

To end the long-running query, you must have the process ID (PID) of the transaction. To find the PID, run the pg_stat_activity query and view the pid column. For more information, see pg_stat_activity on the PostgreSQL website.

Use the pg_cancel_backend(pid) function

When you run the following command from another session, the function uses the PID of the long-running query to cancel the query from the database backend:

SELECT pg_cancel_backend(8121);

Note: In the preceding command, the PID of the query is 8121.

Expected output:

pg_cancel_backend
 ------------------------
 t

In the preceding output, the t value for "true" shows that the function canceled the query. If the query no longer exists or there's no active database connection, then the output shows an f value for "false".

Use the pg_terminate_backend(pid) function

When you run the following command from a different session, the function ends the database connection with pid 8121:

SELECT pg_terminate_backend(8121);

Expected output:

pg_terminate_backend 
------------------------
 t

The preceding output shows t even though the function didn't cancel the query. The response shows that the function successfully sent the SIGTERM signal. The function doesn't immediately interrupt the backend process. To keep the shared memory in a consistent state, the command initiates a graceful shutdown process during CHECK_FOR_INTERRUPTS.

Cancel a long-running process that doesn't end

When you run pg_cancel_backend(pid) or pg_terminate_backend(pid) in an interruptible section, the functions can't cancel the query. For example, the process tries to acquire a lightweight lock. Or, the process is waiting for a read or write system call from storage to complete. In these cases, the backend process doesn't receive the cancellation signal, and the process indefinitely stalls.

If the process doesn't respond to cancellation methods, then restart the entire database engine and forcefully end the stalled process.

It's a best practice to tune timeout parameters, such as statement_timeout, idle_in_transaction_session_timeout and idle_session_timeout for PostgreSQL versions 14 and later. It's also a best practice to configure client-side and server-side timeouts, such as tcp_keepalives_idle, tcp_keepalives_interval, and tcp_keepalives_count.

Note: Because these timeout parameters are dynamic, you don't need to reboot your database for changes to occur.

Configure the parameters based on your requirements. For example, you can set the parameters at the following levels:

• The individual statement level for specific queries
• The user level for all queries from a specific user
• The database level to control behavior across an entire database
• The instance parameter group level to establish global settings

Note: Because a short timeout cancels intentional long-running queries, don't set a short timeout at an instance or database level. If you set log_min_error_statement to ERROR or lower, then Amazon RDS logs the statement that timed out. For more information, see Statement behavior on the PostgreSQL website.

Related information

How do I check running queries and diagnose resource consumption issues for my Amazon RDS for PostgreSQL or Aurora PostgreSQL DB instance?

How do I identify and troubleshoot performance issues and slow-running queries in my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance?