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.