Terminating long-running queries

Aiven does not terminate any customer PostgreSQL queries even if they run "forever".

Long-running queries often cause issues by locking resources and preventing database maintenance tasks.

To identify such queries:

• In the Aiven web console, go to the Current Queries tab for your PostgreSQL service. Here you can also click Terminate to stop any queries directly.

  

• Call the following function over a database connection to terminate a query manually:

  SELECT pg_terminate_backend(pid);

  Note: you may see the following error if the database is owned by a role that your user is not a member of:

  ERROR:  must be a member of the role whose process is being terminated or member of pg_signal_backend

  To find out the roles assigned to each user you can use:

  SELECT r.rolname as username,r1.rolname as "role"
  FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
  ON (m.member = r.oid)
  JOIN pg_roles r1 ON (m.roleid=r1.oid)
  WHERE r.rolcanlogin
  ORDER BY 1;
   username |        role
  ----------+---------------------
   avnadmin | pg_read_all_stats
   avnadmin | pg_stat_scan_tables
  (3 rows)

  To check the database owner and grant the role:

  \l
     Name    |  Owner   |
  -----------+----------+
   testdb    | testrole |
  
  grant testrole to avnadmin;
  GRANT ROLE

You can use the following query to monitor currently running queries:

SELECT * FROM pg_stat_activity WHERE state <> 'idle';

Client applications can use the statement_timeout session variable to voluntarily request the server to automatically cancel any query using the current connection that runs over a specified length of time. For example, the following would cancel any query that runs for more 15 seconds automatically:

SET statement_timeout = 15000

See the PostgreSQL documentation for more information on the available session variables.