Re: Racing DEADLOCK on PostgreSQL 9.3

On 25/04/18 09:21, Nick Dro wrote:
> 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 does stock_aftertrigger do?

> 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 ran also this query:
> SELECT
> pid,
> now() - pg_stat_activity.query_start AS duration,
> query,
> state, *
> FROM pg_stat_activity
> WHERE waiting

What was the full output of this query?

> 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?

"SELECT * FROM pg_locks;" might give a clue.

- Heikki