Postgres database is locked: queries run forever

Question

One of my python scripts ran some ALTER TABLE queries in a Postgres database. Something wrong happened and the tables were locked. When I run any query in any of these tables, it tells me that Query Running and nothing happens. Currently, I can remove this lock only by shutting down my system and restarting it. Please tell me the best 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 will 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 the expanded conclusion to obtain the output data by columns, it is better to view such information.

The following SQL script will display the lock tree (if there are locked sessions), the sessions at the top of each branch (yes, quite often there are several branches) will be blocking.

I advise you to also take a look at this wiki page and this question: Postgresql DROP TABLE does not work (although it talks about DROP TABLE there, it might help).

In your case, I recommend that you identify blocking sessions and try to find out why they are blocking. The most typical case from my experience is that someone forgot to press enter after COMMIT and went out for lunch. If you are sure that this will not harm your system, you can kill the session lock:

 SELECT pg_terminate_backend(pid); 

Answer 2

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