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!