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@xxxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
