Thread: TRUNCATE locking problem
Hello all, We am running PostgreSQL 7.4.5 and recently we have noticed some strange behaviour with regards to the TRUNCATE statement. I think it would help to provide a quick overview of what we are doing with the table in question in order to properly explain this. The application which are using the database require frequent access to this table to perform lookups and all of these are via SELECT statements. Most of these join the table in one way or the other to perform the lookup needed. Every so often, once per day or so, a process will receive new data to populate this table. Once the data is received the process TRUNCATEs the table and then performs a COPY operation to repopulate the table. There is also an autovacuum process which routinely VACUUMs the database though the logs do not show that it is vacuuming when this happens. The behaviour we are experiencing is that the TRUNCATE statement will aquire an ACCESS_EXCLUSIVE lock on the table and then go in to a waiting state. While TRUNCATE is in this state no other process can SELECT on this table. 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. Is this what is actually going on or am I missing something else? and is there a way to prevent this condition from happening? Thanks in advance, - Joe Maldonado
* Joe Maldonado (jmaldonado@webehosting.biz) wrote:
> 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.
>
> Is this what is actually going on or am I missing something else? and is
> there a way to prevent this condition from happening?
TRUNCATE isn't MVCC-safe, for one thing. For another, yes, it uses a
much heavier lock on the table. If you don't want to use a heavy lock
on the table then you'll need to delete from *;. I've got a similar
setup to you and was looking at using truncate for it but I've been
starting to think just interjecting a vacuum in the middle might be
better. ie:
Instead of using:
truncate x;
insert into x;
Doing:
delete from x;
vacuum x;
insert into x;
I'm not really sure which would be faster, so I'm kind of curious about
that. In my case people are rarely using the table at the same time
they'd be truncating/delete'ing it (the UI doesn't actually allow it) so
the total time may be close between the two. For your case that might
not be true since the vacuum might not be able to do much due to the
other select's, which means the table ends up being double the size due
to the old tuples, etc. If someone else has a better solution I'd love
to hear it.
Thanks,
Stephen
Attachment
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
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 > >
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
Joe Maldonado <jmaldonado@webehosting.biz> writes:
> 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.
> 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?
No. That would be a deadlock and would be reported as such.
regards, tom lane
Tom Lane wrote: >Joe Maldonado <jmaldonado@webehosting.biz> writes: > > >>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. >> >> > > > >>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? >> >> > >No. That would be a deadlock and would be reported as such. > > regards, tom lane > > Again many thanks :) - Joe Maldonado