Re: Log messages regarding automatic vacuum and exclusive locks

"jonesd(at)xmission(dot)com" <jonesd(at)xmission(dot)com> wrote:

> I'm seeing several of the following log messages each
> morning (for example, there were five this morning, spaced
> approximately one minute apart, with the closest interval between
> messages being 44 seconds).  They're occurring during a daily cycle of
> deleting all rows from a table and then repopulating it from another
> database; the first message came approximately seven minutes after the
> process started and the last one came about a minute before the
> process ended.  There are approximately one million rows in the
> database after repopulation.
>
> LOG:  automatic vacuum of table "x.y.z": could not (re)acquire
> exclusive lock for truncate scan
>
> I'm using default settings for autovacuum, with server version 9.2.4.
>
> Some other smaller tables produce the same messages during the same
> process, but apparently less frequently.
>
> Any thoughts on the cause?  Chapters 18 and 23 of the documentation
> provided some hints, but nothing conclusive.

As long as these messages stop before the statistics for the
related table have a chance to get out line with reality, these
messages can be safely ignored.  They are happening because
autovacuum noticed a large amount of free space at the end of the
table, and was attempting to remove that space from the table and
give it back to the filesystem, but noticed conflicting access to
the table.  It steps aside to avoid holding up the other process,
and is just making a note of that.  If the table is being
repopulated, it is probably just as well that autovacuum does not
truncate the table, since that would just result in reallocation as
rows are added.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company