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 ?

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