2

I have a simple query:

SELECT a.name AS a_name,
       b.description AS b_description
    FROM a
    JOIN b ON a.id = b.a_id
    WHERE b.table_name IS NULL;

(Names changed to protect the innocent)

This usually takes ~3ms to run. Sometimes, this query hangs. It has currently been hanging for 3 days. Luckily, I'm in a position where I can try and debug this in its current state, for a while. It appears there's nothing blocking it (found by querying pg_locks and pg_stat_activity), though it is blocking other things.

Even with the other query in a hung state, I can run the same query again, and it completes successfully in the expected time period.

Things that are likely to be involved: we currently have two cron jobs (don't ask) that dump the database. I can find the exact commands being run if it's likely to be relevant.

Any suggestions on how to debug this gratefully received!

  • Try to rewrite the query to NOT EXISTS. – Akina Jan 22 at 17:04
  • for the query to hang for 3 days I suspect something else is going on... Is the session sitting idle or is it still chunking away 100% cpu usage?? – zsheep Jan 22 at 17:17
  • Can you find it in pg_stat_activity? What do you see for wait_event_type , wait_event , state – jjanes Jan 22 at 17:18
1

Check the wait events in pg_stat_activity to see if your query is running or hanging.

In the latter case, examine pg_locks or use the pg_blocking_pids function to see which session blocks you.

If no locks are involved, see if the backend process consumes CPU or not. Optionally use strace to see what the backend is doing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.