On Thu, May 31, 2018 at 9:19 AM, Ashu Pachauri <ashu210890@xxxxxxxxx> wrote:
There was too much noise in the pg_stat_activity output, so I did not post it. I'll collect the output again and post.But, when I checked in pg_stat_activity, PID 18317 is the session that's running the ALTER statement and it was showing up as "active". So, it's not blocked by anything, but the fact that the ALTER statement is long running and it's blocking the operations that are not even on the same table for the entire duration it's running is troubling.
I think what you really need to know here is what lock it was holding which was blocking everyone. That information won't be found in the pg_stat_activity. It will be found in pg_locks, but the specific query you ran on that view did not display the columns with that information. You need to include all the columns in the output which you used to join the two pg_locks together. Yes, it will be voluminous, and most of them will not be relevant, but you don't know which ones are relevant until after you see the output.
Cheers,
Jeff
