Re: Log messages regarding automatic vacuum and exclusive locks

I'll give using TRUNCATE to clear the tables a try and see what happens.


Dominic Jones


Quoting Sergey Konoplev <gray.ru@gmail.com>:

> On Tue, Apr 23, 2013 at 8:50 AM,  <jonesd@xmission.com> wrote:
>> Good morning.  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
>
> If all the rows are deleted from the table every time to repopulate it
> later, I would suggest using TRUNCATE table_name; instead DELETE FROM
> table_name. TRUNCATE does not left any free space in the table so
> autovacuum will have nothing to do with it.
>
>> LOG:  automatic vacuum of table "x.y.z": could not (re)acquire exclusive
>> lock for truncate scan
>
> Hence you will not get this messages and BTW it will be faster.
>
>>
>> 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.
>>
>>
>> Thanks in advance,
>>
>> Dominic Jones
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> Kind regards,
> Sergey Konoplev
> Database and Software Consultant
>
> Profile: http://www.linkedin.com/in/grayhemp
> Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
> Skype: gray-hemp
> Jabber: gray.ru@gmail.com
>