Обсуждение: Autovaccum and Full vacuum

Hi Folks

My apologies if this is documented - I could not find it.

Are there circumstances under which an autovacuum will do a "full vacuum"?
I guess what I would really like to know is - is it possible that autovacuum will request a AccessExclusiveLock and if yes, under what circumstances?

Thanks

Regards, Tony

On Wed, May 5, 2010 at 2:28 PM, Tony Day <tonyd@panztel.com> wrote:IIRC, there was a similar bug in pre 8.3. Which version are you using?

--
Shoaib Mir
http://shoaibmir.wordpress.com/

Hi Shoaib

Thanks for the response.

Sorry - I forgot to mention that I am using PostgreSQL 8.3.9 on Ubuntu 9.04.
And just to be clear - I am not suggesting that there is a bug in Postgresql :-)
I am just trying to clarify what it does and doesn't do.

Regards, Tony

On Wed, May 5, 2010 at 2:46 PM, Tony Day <tonyd@panztel.com> wrote:
--
Shoaib Mir
http://shoaibmir.wordpress.com/

On Wed, May 5, 2010 at 12:01 AM, Shoaib Mir <shoaibmir@gmail.com> wrote:
> On Wed, May 5, 2010 at 2:46 PM, Tony Day <tonyd@panztel.com> wrote:
>>
>> Hi Shoaib
>>
>> Thanks for the response.
>>
>> Sorry - I forgot to mention that I am using PostgreSQL 8.3.9 on Ubuntu
>> 9.04.
>> And just to be clear - I am not suggesting that there is a bug in
>> Postgresql :-)
>> I am just trying to clarify what it does and doesn't do.
>>
>>
>
> You can find on docs autovacuum
> at http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM
> It uses VACUUM and not VACUUM FULL, there shouldn't be a need of VACUUM FULL
> if you have autovacuum enabled with proper thresholds. There can be an odd
> case where you might have to make auto-vacuum a little more aggressive for a
> specific table.

autovacuum never executes VACUUM FULL, but...

in 8.3, sometimes it finds a page that is empty so it truncate it to
release space, and that needs an stronger lock than the normal it uses
(but i don't remember exactly what type of lock) and when the
autovacuum is executing for anti wraparound problems it cannot be
cancelled...

so, if an autovacuum execute vacuum for anti wraparound problems and
it finds empty pages that should be truncated then you get a situation
where a table is locked and you can't terminate the vacuum, if the
table is big there are problems... no idea if this happens in 8.4


--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL