Hello all again...
While researching this locking issue I got some of the logs and found
that in one of the cases there was a SELECT running for a long time,
about 2 hours. This select statement does not usually take more than a
few seconds though, it appeared that TRUNCATE was waiting on it to
finish before continuing.
When I looked at the SELECT statement I found something which I think
may have caused something of a deadlock though since I am quite ignorant
as to how these locks interact I apologize if this is a dumb question :)
The SELECT statement in question contains a sub SELECT in the FROM
clause which in turn is joining with a view that contains the table
which TRUNCATE is being executed against.
Is it possible that the SELECT was issues just before the TRUNCATE
statement was issues and the view in the sub SELECT was waiting on
TRUNCATE's lock?
- Joe Maldonado
Joe Maldonado wrote:
> Thanks...I just wanted to verify that it was the intended behaviour
> prior to going in and changing code :)
>
> - Joe Maldonado
>
> Tom Lane wrote:
>
>> Joe Maldonado <jmaldonado@webehosting.biz> writes:
>>
>>
>>> It seems that TRUNCATE is first posting a lock on the table and then
>>> waiting for other transactions to finish before truncating the table
>>> thus blocking all other operations.
>>>
>>
>>
>> That's what it's supposed to do. If you have a problem with the length
>> of the delay, find the (other) transaction that's accessing the table
>> for too long and fix that.
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq