Thread: "canceling autovacuum time"

"canceling autovacuum time"

From
Jameison Martin
Date:
I'm seeing "GMTERROR: canceling autovacuum task" lines in my logs.


2012-02-27 23:53:28 GMTLOG:  checkpoint starting: time
2012-02-27 23:53:31 GMTERROR:  canceling autovacuum task
2012-02-27 23:53:31 GMTCONTEXT:  automatic vacuum of table "<somedb>.pg_toast.pg_toast_33254"
2012-02-27 23:53:32 GMTERROR:  canceling autovacuum task
2012-02-27 23:53:32 GMTCONTEXT:  automatic vacuum of table "<somedb>.pg_toast.pg_toast_33485"
2012-02-27 23:54:29 GMTLOG:  automatic vacuum of table "<somedb>.pg_toast.pg_toast_33254": index scans: 1

It was suggested to me that perhaps I had an exclusive lock on the table that was being vacuumed (e.g. that I was running some DDL that was conflicting with the autovacuum). I'm quite certain that I'm not running any DDL at the time this happens, all i'm running are:
  • SELECT ... FOR UPDATE
  • UPDATE
  • INSERT
  • DELETE
Does anyone have a suggestions as to what the cause of this error might be?  

I'm using Postgres 9.0.

Thanks.

Re: "canceling autovacuum time"

From
Ondrej Ivanič
Date:
Hi,

On 28 February 2012 11:53, Jameison Martin <jameisonb@yahoo.com> wrote:
> I'm seeing "GMTERROR: canceling autovacuum task" lines in my logs.

That's *should* be fine. autovacuum daemon is smart enough to cancel
it self when other query needs access to the table. The affected table
will be vacuumed/analysed later. You should monitor
pg_stat_user_tables view to see how often is the table
vacuumed/analysed.

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)

Re: "canceling autovacuum time"

From
"Tomas Vondra"
Date:
On 28 Únor 2012, 1:53, Jameison Martin wrote:
> I'm seeing "GMTERROR: canceling autovacuum task" lines in my logs.

It's not "GMTERROR", it's "GMT ERROR" where GMT is a timezone. You should
put a space at the end of log_line_prefix I guess.

> 2012-02-27 23:53:28 GMTLOG:  checkpoint starting: time
>>2012-02-27 23:53:31 GMTERROR:  canceling autovacuum task
>>2012-02-27 23:53:31 GMTCONTEXT:  automatic vacuum of table
>> "<somedb>.pg_toast.pg_toast_33254"
>>2012-02-27 23:53:32 GMTERROR:  canceling autovacuum task
>>2012-02-27 23:53:32 GMTCONTEXT:  automatic vacuum of table
>> "<somedb>.pg_toast.pg_toast_33485"
>>2012-02-27 23:54:29 GMTLOG:  automatic vacuum of table
>> "<somedb>.pg_toast.pg_toast_33254": index scans: 1
>
>
> It was suggested to me that perhaps I had an exclusive lock on the table
> that was being vacuumed (e.g. that I was running some DDL that was
> conflicting with the autovacuum). I'm quite certain that I'm not running
> any DDL at the time this happens, all i'm running are:
>     * SELECT ... FOR UPDATE
>     * UPDATE
>     * INSERT
>     * DELETE
> Does anyone have a suggestions as to what the cause of this error might
> be?

Generally yes, autovacuum can cancel itself is something holds a lock.
Check pg_locks what locks are there
(http://www.postgresql.org/docs/9.1/static/view-pg-locks.html).

Tomas