try "Сombination of blocked and blocking activity" from https://wiki.postgresql.org/wiki/Lock_Monitoring - it should show you the originator. pg_stat_activity.waiting only show affected
On 25 April 2018 at 19:56, Jerry Sievers <gsiever...@comcast.net> wrote: > Nick Dro <postgre...@walla.co.il> writes: > > > Hi, > > I have a stock table. > > > > One of the users in the system ran this query: update stock set > > quantity=quantity-5 where stockid=100 (from his client application). > > On the same time I ran from pg-admin this query: > > > > do $$ > > begin > > alter table stock disable trigger stock_aftertrigger; > > update stock set stock=0 where stockid=106; > > alter table stock enable trigger stock_aftertrigger; > > end; $$ > > > > What actualy happened is that both queries were stuck on waiting > > (after 3 minutes I decided to investagate as there quries should be > > extremly fast!). > > I suspect your alter trigger job was blocked first by something else and > the more trivial update blocked behind you, which is not a *deadlock* > but a legit case of MVCC. > > A real case of deadlock should have been broken in about 1s by the lock > management policy unless you are running a configuration with huge > deadlock timeout. > > That your alter statement needs a heavy lock means that it can be easily > blocked and in so doing, block anything else whatsoever also requiring > access to same objects. > > > I ran also this query: > > > > SELECT > > pid, > > now() - pg_stat_activity.query_start AS duration, > > query, > > state, * > > FROM pg_stat_activity > > WHERE waiting > > > > > > and both users were on waiting. When I stopped my query the other > > user got imiddiate result, then I reran mine which also finished > > immidiatly. > > I don't understand why both queries were stuck, the logic thing is > > that one ran and the other one is waiting (if locks aquired etc) it > > doesnt make senece that both queries are on waiting. waiting for what > > exactly? > > > > > > Any thoughts on this issue? > > > > > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consult...@comcast.net > p: 312.241.7800 > >
