...
Find any sessions that have not (yet) been granted a necessary lock:
select * from pg_locks where not granted;
We see here that there are many, all for the same table, which is already suspicious.
We get the table like this:
select * from pg_locks where relation = 16829341
Now we need to see which sessions are holding the locks, i.e. those that have a lock granted:
select * from pg_locks where relation = 16829341 and granted
There are two such sessions. Of course, when they complete, they will no longer be an issue. But when we see they remain there for a long time, it points to an issue.
Find what the sessions owning a lock are doing:
select * from pg_stat_activity where pid = 32370
Notice how long this session has been up, and that it 's is in an 'idle' state. Sometimes it is 's 'active', when it 's for exis e.g. running SQL that takes many hours to complete. In the end, such a session will close, and the issue will be resolved automatically, even though such a slow SQL statement would need to be investigated.
But here it looks like we have a client that disappeared without closing the txn/session, which can happen when the client dies unexpectedly. There should be a timeout for this session in the end, and it could be this is a Greenplum specific issue. Maybe it 's is just a DB setting that needs to be changed. I'll leave that to TechOps to find outThis is best to be evaluated by TechOps.Resolve the issue, here by killing the idle session(s):
select pg_cancel_backen(32370)
The statement returns 'true', which means the session was cancelled, BUT that is not always the case. You need to check again, and resort to usingpg_terminate_backend()if the session does not disappear.
Here it worked fine. Also for the second session, which was also idle:
Now there are no more blocking sessions, all locks have been granted:
...