Hanging Query Troubleshooting

Steps to Identify a Hanging Query in PostgreSQL

1. Long Duration:

Monitor for queries running unusually long without producing results:

SELECT pid, usename, datname, query, state, query_start 
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

2. Wait Event:

Check wait_event_type and wait_event columns to see if the query is in a wait state:

SELECT pid, usename, datname, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid = <your_query_pid>;

3. State of the Query:

Analyze the state column to check if the query is "active" but not making progress.

4. Resource Utilization:

Monitor system resources (CPU, I/O, memory) to see if the query is consuming resources without completing.

5. Check for Blockings:

Determine if the query is waiting on another transaction to release a lock:

SELECT blocked_locks.pid AS blocked_pid, 
       blocked_activity.query AS blocked_query, 
       blocking_locks.pid AS blocking_pid, 
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Conclusion:

Following these steps will help you effectively identify whether a query is hung and may lead you to the necessary actions to resolve the situation.

Resolving Long-Running FETCH Queries in Aurora PostgreSQL(Real Time Example)

If the FETCH 300000 IN cursor_262945937_0 query has been running for two days and you are unable to terminate it using pg_terminate_backend, it suggests the query might be stuck in a problematic state (such as waiting on a lock or I/O operation). In such cases, you can try additional steps to resolve the issue.

Here’s a systematic approach to resolve the issue:

1. Verify the Process and Backend PID

First, make sure you are attempting to terminate the correct process. Use the following query to identify the pid of the backend running the long query:

SELECT pid, usename, datname, query, state, query_start
FROM pg_stat_activity
WHERE query LIKE '%FETCH%' AND state = 'active';

Take note of the pid of the relevant process.

2. Attempt to Kill the Backend Using pg_terminate_backend

If you already tried using pg_terminate_backend and it didn’t work, ensure that:

• You are using the correct PID (from the above query).
• Your user has sufficient privileges (superuser) to terminate the process.

The command to terminate a backend session is:

SELECT pg_terminate_backend(<pid>);

If this still doesn’t work, continue to the next steps.

3. Use pg_cancel_backend Instead

If pg_terminate_backend is not terminating the process, the backend might be in a state where it cannot be terminated immediately. You can first try to cancel the query using pg_cancel_backend:

SELECT pg_cancel_backend(<pid>);

This only cancels the query but doesn’t kill the backend session. It may be helpful if the query is in a state where termination is problematic due to ongoing resource locks or I/O waits.

4. Check for Locks and Blocking Queries

The query might be stuck waiting on a lock, which could prevent termination. Use the following query to see if the process is waiting on a lock or being blocked:

SELECT blocked_locks.pid AS blocked_pid, 
       blocked_activity.query AS blocked_query, 
       blocking_locks.pid AS blocking_pid, 
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

This will show if any query is blocking the FETCH command. If you find a blocking query, you can attempt to terminate the blocking session.

5. Check for System-Level Issues (I/O Waits or CPU)

Sometimes queries can hang due to underlying system issues, such as I/O bottlenecks, insufficient resources, or other hardware problems. You can check system-level information (if you have access to the EC2 instance or monitoring tools) to see if there are any bottlenecks or high CPU/I/O utilization.

6. Terminate the Backend Session at the OS Level

If all of the above methods fail and the query remains stuck, you can forcibly kill the backend session at the operating system level. Follow these steps:

1. Get the OS PID associated with the backend process from the previous query (pg_stat_activity).
2. SSH into the EC2 instance running Aurora PostgreSQL (if possible).
3. Use the following command to kill the process:

kill -9 <os_pid>

7. Last Resort: Restart the Aurora PostgreSQL Instance

If none of the above methods work and the query is completely stuck without being terminated, you may need to restart the Aurora PostgreSQL instance. While this is disruptive, it will forcibly clear all sessions and long-running queries.

Steps to Restart:

1. Navigate to your AWS Management Console.
2. Go to RDS → Databases → Select your Aurora PostgreSQL cluster.
3. Choose Actions → Restart.

Summary of Actions:

1. Verify the correct pid and attempt pg_terminate_backend again.
2. Try using pg_cancel_backend if termination fails.
3. Check for locks and blocking queries using the lock-check query.
4. If locked or blocked, terminate the blocking process.
5. Consider OS-level termination (kill -9).
6. As a last resort, restart the Aurora PostgreSQL instance.

Let me know if you need more details on any of these steps!