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:

    Name | Owner |
    testdb | testrole |

    grant testrole to avnadmin;

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.

Did this answer your question?