Postgresql - Why is DROP VIEW command hanging?

freeze, postgresql, sql-drop, view

Solution

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

Problem

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 ?

Original source