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 ?
Finally I figure out what was wrong. Here are the steps to find the root cause:
Solution
Step 1 : List requested locks 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 myDROP VIEW...
hangs.Step 2 : Find which other process(es) held a conflicting lock
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
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: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