Re: TRUNCATE locking problem

* Joe Maldonado (jmaldonado(at)webehosting(dot)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