Postgresql - Why is DROP VIEW command hanging?

Question

I want to perform a simple DROP VIEW ... but it hangs.

I have run this query SELECT * FROM pg_locks WHERE NOT granted taken from this page on Lock Monitoring.

However the following query they suggest returns no results:

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

Where should I look now ?

Simmers

This sounds like a database engine issue; have you tried to do the VACUUM ANALYZE and/or REINDEX on the tables involved in view? Also, restarting Postgres? – Uppity

@Uppity I don't want to just restart Postgres since I won't have the root cause of the hanging. – Simmers

In my case, the hanging was cos' I have an REST service running (FastAPI) using my postgres DB. I just apply a reload on my REST service and my transaction finish without problems. Maybe a reload of services that use you postgres database could solve your hanging, or reload the postgres itself. – Trickster

Answer 1

Finally I figure out what was wrong. Here are the steps to find the root cause:

Solution

Step 1 : List requested locks not granted

select * from pg_locks where not granted;

In my case, an attempt to lock, with the mode AccessExclusiveLock, the view I want to drop was not granted. This is why my DROP VIEW... hangs.

Step 2 : Find which other process(es) held a conflicting lock

select * from pg_locks where relation = <oid_of_view>

Here I list all processes locking or trying to lock on my view. I found out two processes, the one that want to drop the view and... another one.

Step 3 : Find out what other process(es) is/are doing now

select xact_start,query_start,backend_start,state_change,state from pg_stat_activity where pid in (<list_of_other_process(es)_pid>);

I had only one process holding a lock in my case. Surprisingly, its state was : idle in transaction

I was not able to drop the view because another process was idle in transaction. I simply kill it to solve my issue. For example, if the procpid was 8484 and let's suppose my postgresql server runs on a Linux box, then in the shell, I execute the following command:

$ kill -9 8484

Discussion

If you face similar issue, you can quickly find out what's going on by reproducing steps 1,2,3. You may need to customize Step 2 in order to find other conflicting process(es).

References

• Lock Monitoring
• Lock Dependency Information
• View Postgresql Locks

Simmers

For reference, this is steps 1-3 in one query: select * from pg_stat_activity where pid in (select pid from pg_locks where relation = (select relation from pg_locks where not granted)); – Righthanded

I did run the kill command and that ended postgresql to recovery mode for a minute or so after which I could connect with a client to it again. Might be that the answer by @borhan-kazimipour is safer. Anyway, the lock was not anymore hanging, so I could complete the SQL query that I was trying to do. – Gogol

Answer 2

I had a similar problem but the accepted answer didn't work for me as I do not have admin access to kill any process. Instead, this is how I managed to solve the problem:

1. Issue SELECT * FROM pg_stat_activity; to get the stats about the PostgreSQL activities.
2. In query column, look for the queries that read from that view. You may choose to narrow down your search by only looking into the rows related to your username (using username column) or query_start if you know when the issue emerged. There could be more than one row associated with your unwanted view.
3. Identify all pid from the rows in the above step and plug them into SELECT pg_terminate_backend(<pid>); (instead of <pid>) one by one and run them.

Now you should be able to drop your view.

Please note that as you terminate the backend processes using pg_terminate_backend(), you may face some errors. The reason is that terminating some process may automatically end other processes. Therefore, some of the identified PIDs might be invalid by the time.

Fenner

Answer 3

As a summary, this solution from comments worked for me:

Step 1: Find the pid:

select * from pg_stat_activity 
  where pid in 
    (select pid from pg_locks 
      where relation = 
        (select relation from pg_locks where not granted));

Step 2: kill pid:

kill -9 pid

Triboluminescence