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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Pomarede Nicolas" <npomarede@xxxxxxxxxxxx> 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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux