Re: Long running DDL statements blocking all queries

On Thu, May 31, 2018 at 9:19 AM, Ashu Pachauri <ashu210890(at)gmail(dot)com> 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