Re: Long running DDL statements blocking all queries

Ashu, 

please, provide full output of:


\x
select * from pg_stat_activity ;



What you posted on github is only providing a list of blocked backends.

If I read it correctly, then PID 18317 is the root cause of all the locks, but it does not show up in the list, not
beingblocked by anything...
 


regards,

fabio pardi 



On 31/05/18 14:26, Ashu Pachauri wrote:
> Thanks Fabio for the reply.  
> The queries are blocked in the sense that I can see them in pg_stat_activity.
> 
> Please find the query and its output for correlating the blocked activity with blocking query from pg_state_activity
andpg_locks: https://gist.github.com/ashu210890/c39cd7a38ce37f4baab2f58e1ade1403
 
> 
> This output was captured after stopping all writes to our postgres database and the only thing talking to postgres
wasour webserver that only does metadata reads. As you can see from the above gist, even the 'SET' statements are
blockedwaiting for the ALTER statement to finish.
 
> 
> 
> Thanks,
> Ashu
> 
> 
> On Thu, May 31, 2018 at 4:38 PM Fabio Pardi <f.pardi@portavita.eu <mailto:f.pardi@portavita.eu>> wrote:
> 
>     Hi Ashu,
> 
>     when you say 'almost every query in our application starts getting blocked'...
> 
> 
>     'blocked' as in 'they are stuck and can be seen in pg_stat_activity'? (in this case, please post the full content
ofpg_stat_activity)
 
> 
>     or
> 
>     'blocked' as in 'they are waiting in pgbouncer pool?
> 
> 
>     regards,
> 
>     fabio pardi
> 
> 
>     On 31/05/18 12:38, Ashu Pachauri wrote:
>     > We have been using Postgres 9.5.12 behind PGBouncer and facing some weird issues. Whenever we running long
runningDDL statements (e.g. 'add index concurently' or 'Alter table alter column type'), after some time, we start
seeingthat almost every query in our application starts getting blocked.
 
>     > I understand that the operations I mentioned cab be unsafe, but the queries being blocked are on completely
unrelatedtables.  I used the instructions given on Postgres wiki (https://wiki.postgresql.org/wiki/Lock_Monitoring) to
correlatethe blocking and blocked statements and there seems to be absolutely no correlation.
 
>     >
>     > Thanks,
>     > Ashu
>