Postgres Database locked: Queries running forever

Question

One of my python scripts ran some ALTER TABLE queries on the Postgres database. Something wrong happened and the tables got locked. When I run any query on any of those tables, it tells me Query Running and nothing happens. Currently, I am able to remove this lock only by shutting down my system and restarting it. Please tell me a better method. This is a Windows host.

Answer 1

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);

Answer 2

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;