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