Postgres Professional
   

Re: "slow" queries

From: Tom Lane
Subject: Re: "slow" queries
Date: ,
Msg-id: 18268.1235959054@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: "slow" queries  ("Cox, Brian")
List: pgsql-performance

Tree view

"slow" queries  (Brian Cox, )
 Re: "slow" queries  (Robert Haas, )
 Re: "slow" queries  (Tom Lane, )
 Re: "slow" queries  ("Cox, Brian", )
  Re: "slow" queries  (Tom Lane, )
 Re: "slow" queries  (Brian Cox, )
  Re: "slow" queries  (Scott Carey, )
  Re: "slow" queries  (Robert Haas, )
  Re: "slow" queries  (Tom Lane, )
   Re: "slow" queries  (Tim Bunce, )
    Re: "slow" queries  (Scott Marlowe, )
 Re: "slow" queries  (Brian Cox, )
  Re: "slow" queries  (Tom Lane, )
 Re: "slow" queries  (Brian Cox, ) 
"Cox, Brian" <> writes:
>> Probably because the DROP is trying to acquire exclusive lock on its
>> target table, and some other transaction already has a read or write
>> lock on that table, and everything else is queuing up behind the DROP.

>> It's not a true deadlock that is visible to the database, or else
>> Postgres would have failed enough of the transactions to remove the
>> deadlock.  Rather, what you've got is some very-long-running transaction
>> that is still making progress, or else is sitting idle because its
>> client is neglecting to close it; and everything else is blocked behind
>> that.

> This "deadlock" finished after 18h and 48m. As there is only 1 select
> on a table with 400 rows and 10 inserts into a separate partition than
> the one being dropped, what could possible take 18:48 to do?

[ shrug... ]  You tell us.  To me it sounds a whole lot like some client
program sitting on an open transaction that has a nonexclusive lock on
the table to be dropped.  That transaction wasn't necessarily doing any
useful work; it might have just been waiting on the client.

At this point I suppose arguing about it is moot because the evidence
is all gone.  If it happens again, capture the contents of pg_locks and
pg_stat_activity while things are still stuck.

            regards, tom lane

pgsql-performance by date:

Previous:
From: Tom Lane
Date:
Subject: Re: "slow" queries
Next:
From: Brian Cox
Date:
Subject: Re: "slow" queries