Detect and terminate long-running queries#
Aiven does not terminate any customer queries even if they run indefinitely, but long-running queries can cause issues by locking resources and therefore preventing database maintenance tasks.
To identify and terminate such long-running queries, you can do it from either:
Terminate long running queries from the Aiven Console#
In the Aiven Console, you can go to the Current Queries tab for your service.
On the Current Queries page, you can check the query duration and click Terminate to stop any long-running queries.
Detect and terminate long running queries with
You can login to your service by running on the terminal
psql <service_uri>. Once connected, you can call the following function on the
psql shell to terminate a query manually:
You can learn more about the
pg_terminate_backend() function from the official documentation.
You can then 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
You may check the client connection defaults documentation for more information on the available session variables.
Database user error#
If you run the above command using a database user not being a member of the database you’re connecting to, you will encounter the error:
ERROR: must be a member of the role whose process is being terminated or member of pg_signal_backend
You can check the roles assigned to each user with the following command:
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;
where you would see the following:
username | role ----------+--------------------- avnadmin | pg_read_all_stats avnadmin | pg_stat_scan_tables (3 rows)
To be able to check the database owner and grant the role, you can run the following:
which you should see the role:
Name | Owner | -----------+----------+ testdb | testrole |
To resolve the permission issue, you may grant the user the appropriate role as per below:
grant testrole to avnadmin;