Skip to content

Instantly share code, notes, and snippets.

@benregn
Created May 9, 2019 15:11
Show Gist options
  • Save benregn/3c0387bb7415acd3c9c3bb91554a3299 to your computer and use it in GitHub Desktop.
Save benregn/3c0387bb7415acd3c9c3bb91554a3299 to your computer and use it in GitHub Desktop.
Debugging stuck queries in postgres (lock)

From: https://stackoverflow.com/questions/26596566/postgres-database-locked-queries-running-forever

You should check for locks:

SELECT l.*,a.*
  FROM pg_locks l
  JOIN pg_stat_activity a USING (pid)
 WHERE NOT granted;

You'll see a list of waiting sessions. And the following:

SELECT l.*,a.*
  FROM pg_locks l
  JOIN pg_stat_activity a USING (pid)
 WHERE granted
   AND (database,relation) IN (SELECT database,relation
                                 FROM pg_locks WHERE NOT granted);

will give you a list of blocking sessions. If you use psql, use expanded output to get column-per-row output, better to view such information.

I advise you to also have a look at this wiki page and this question: Postgresql DROP TABLE doesn't work (though it speaks bout DROP TABLE there, it might help).

In your case, I recommend to identify blocking sessions and try to find out why they're blocking. Most typical case in my experience — somebody forgot to press enter after COMMIT and went out for lunch. If you're sure this will not hurt your system, you can kill blocking session:

SELECT pg_terminate_backend(pid);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment