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

4 minute read
1

I want to end a long-running process in my Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition DB instance.

Short description

Use pg_cancel_backend(pid) to end a query but keep the connection alive. Use pg_terminate_backend(pid) to end a query and close the connection. This function ends the full connection and might affect other queries in progress. Use pg_terminate_backend(pid) only when necessary.

To use the functions, you must have one of the following permissions:

  • 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.

Note:

  • Use the pid column of the pg_stat_activity view to find the process ID (PID) of an active backend process. For more information, see pg_stat_activity on the PostgreSQL website.
  • pg_signal_backend sends the SIGINT signal and pg_terminate_backend sends the SIGTERM signal to the backend process. For more information, see Server signaling functions on the PostgreSQL website.

Resolution

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

"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.

The following are examples of the pg_cancel_backend(pid) and pg_terminate_backend(pid) functions in use:

pg_cancel_backend(pid)

When you run the following function from another session, it cancels the query that runs from the database backend with pid 8121

postgres=> SELECT pg_cancel_backend(8121);

Expected output:

pg_cancel_backend 
------------------------
 t
(1 row)

When you run the followingthe query is correctly canceled, the function returns true (t). If the query no longer exists or the database connection doesn't exist, then the function returns false (f).

pg_terminate_backend(pid)

When run from another session, the following function ends the database connection with pid 8121:

postgres=> SELECT pg_terminate_backend(8121);

Expected output:

pg_terminate_backend 
------------------------
 t
(1 row)

The function returns true (t) even when the process isn't canceled yet. The response shows only that the SIGTERM signal was successfully sent. The command doesn't immediately interrupt the backend process. To keep the shared memory in an inconsistent state, the command initiates a graceful shutdown process that happens during CHECK_FOR_INTERRUPTS.

Cancel a long-running process that doesn't end

When you run pg_cancel_backend(pid) and pg_terminate_backend(pid) in an interruptible section, the functions don't successfully cancel the query. For example, the process might be waiting to acquire a lightweight lock or a read or write system call against a network storage device. The backend process isn't signaled and stalls indefinitely.

To end the process, restart the database engine.

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

You can set the statement_timeout parameter at appropriate levels, such as statement, user level, database, or instance.

Note: It isn't best practice to set a short timeout at an instance level or database level because a short timeout cancels intentional long-running queries. If log_min_error_statement is set to ERROR or lower, then the statement that timed out gets logged. 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 PostrgreSQL 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?

1 Comment

Some earlier versions of RDS and Aurora cannot terminate an autovacuum process even following the requirements above, and will show the following error "ERROR: 42501: must be a superuser to terminate superuser process LOCATION: pg_terminate_backend, signalfuncs.c:227"

This was patched recently for some minor versions, and can be seen in the documentation below under General Enhancements where it states, "Allow rds_superuser to terminate backends which are not explicitly associated with a role: [+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html

Can we please add a note to this post outlining this, thanks.

profile pictureAWS
SUPPORT ENGINEER
replied 3 months ago