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
