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.
The following SQL script will display blocking tree (if there are blocked sessions), sessions on the top of each branch (yes, quite often there're several branches) will be the blocking ones.
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);
Reference taken from this article. Find blocking sessions:
SELECT
pl.pid as blocked_pid
,psa.usename as blocked_user
,pl2.pid as blocking_pid
,psa2.usename as blocking_user
,psa.query as blocked_statement
FROM pg_catalog.pg_locks pl
JOIN pg_catalog.pg_stat_activity psa
ON pl.pid = psa.pid
JOIN pg_catalog.pg_locks pl2
JOIN pg_catalog.pg_stat_activity psa2
ON pl2.pid = psa2.pid
ON pl.transactionid = pl2.transactionid
AND pl.pid != pl2.pid
WHERE NOT pl.granted;