Re: truncate a table instead of vaccum full when count(*) is 0

"Pomarede Nicolas" <npomarede(at)corp(dot)free(dot)fr> writes:

> Yes, I already do this on another spool table ; I run a vacuum after processing
> it, but I wondered if there was another way to keep the disk size low for this
> table.

"after processing it" might be too soon if there are still transactions around
that are a few minutes old and predate you committing after processing it.

But any table that receives as many deletes or updates as these tables do will
need to be vacuumed on the order of minutes, not days.

>> It should work, just like you describe it, with the caveat that TRUNCATE will
>> remove any old row versions that might still be visible to an older
>> transaction running in serializable mode.
>
> Shouldn't locking the table prevent this ? I mean, if I try to get an exclusive
> lock on the table, shouldn't I get one only when there's no older transaction,
> and in that case I can truncate the table safely, knowing that no one is
> accessing it due to the lock ?

It would arise if the transaction starts before you take the lock but hasn't
looked at the table yet. Then the lock table succeeds, you truncate it and
commit, then the old transaction gets around to looking at the table.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com