Re: MultiXactId Error in Autovacuum

Thank you for your suggestion!

Since the autovacuum process is failing each time because of: "ERROR:
MultiXactId 2683601542 does no longer exist -- apparent wraparound", I
have expected the manual vacuum also to fail because of the same error.
I've tried it anyway. Well ... manual vacuum also fails unfortunately.
I did not run vacuum with --full, because I want to avoid a locked table.

BR,
Karl

On Sat, Nov 16, 2013 at 9:25 PM, Strahinja Kustudić
<strahinjak(at)nordeus(dot)com>wrote:

> If autovacuum doesn't want to finish the vacuuming of that table, why not
> just kill the autovacuum process on that table (you could use
> *pg_cancel_backend()*)and run vacuum manually on that table?
>
>
> Strahinja Kustudić | Lead System Engineer | Nordeus
>
>
> On Fri, Nov 15, 2013 at 10:33 AM, Karl Hafner <karl(at)scoreloop(dot)com> wrote:
>
>> Sorry, dear mailing list, somehow my email got sent before I finished it
>> :-(
>>
>> I will go on and repeat. Please bear with me!
>>
>>
>> I am currently worried about an error that shows up in our log files
>> since a few weeks:
>>
>> 2013-11-15 07:13:13 UTC [22668]: [2-1] ERROR: MultiXactId 2683601542
>> does no longer exist -- apparent wraparound
>> 2013-11-15 07:13:13 UTC [22668]: [3-1] CONTEXT: automatic vacuum of
>> table "scoreloop.public.gamer_device_sightings"
>>
>> It is always the same MultiXactId.
>> It seems that "autovacuum" is active on that table, but it seems to have
>> restarted today in the morning:
>>
>> select query_start, query, state, pid from pg_stat_activity where query
>> LIKE 'autovacuum%' ;
>>
>> # select query_start, query, state, pid from pg_stat_activity where query
>> LIKE 'autovacuum%' ;
>>
>> query_start | query
>> | state | pid
>>
>> -------------------------------+----------------------------------------------------------------------------------+--------+------
>> 2013-11-15 07:13:13.281202+00 | autovacuum: VACUUM ANALYZE
>> public.gamer_device_sightings (to prevent wraparound) | active | 4360
>>
>>
>> There are a few locks on tat table:
>>
>> # select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c join
>> pg_locks l on c.oid=l.relation where l.pid='4360' order by l.pid;
>> oid | relname | pid | mode
>> | granted
>>
>> -------+------------------------------------------+------+--------------------------+---------
>> 19862 | index_gamer_device_sightings_on_gamer_id | 4360 |
>> RowExclusiveLock | t
>> 19719 | gamer_device_sightings_pkey | 4360 |
>> RowExclusiveLock | t
>> 19309 | gamer_device_sightings | 4360 |
>> ShareUpdateExclusiveLock | t
>>
>>
>>
>> The error I described seems to terminate the auto vacuum process: It has
>> never finished on that table.
>> It is the only table without a finished vacuum. So I guess the above
>> error is the reason for this.
>>
>> Is there anything that I can do to get that fixed? Should I worry about
>> the error? (I would not if the vacuum would run through).
>>
>> Any tips, any help would be much appreciated!
>>
>> Thank you very much! And sorry once more for my posting mistake
>> Karl
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Fri, Nov 15, 2013 at 10:16 AM, Karl Hafner <karl(at)scoreloop(dot)com> wrote:
>>
>>> Dear mailing-list,
>>>
>>> I am currently worried about an error that shows up in our log files
>>> since a few weeks:
>>>
>>> 2013-11-15 07:13:13 UTC [22668]: [2-1] ERROR: MultiXactId 2683601542
>>> does no longer exist -- apparent wraparound
>>> 2013-11-15 07:13:13 UTC [22668]: [3-1] CONTEXT: automatic vacuum of
>>> table "scoreloop.public.gamer_device_sightings"
>>>
>>> It seems that "autovacuum" is active on that table, but it seems to have
>>> restarted today in the morning:
>>>
>>> select query_start, query, state, pid from pg_stat_activity where query
>>> LIKE 'autovacuum%' ;
>>>
>>> XXX=# select query_start, query, state, pid from pg_stat_activity where
>>> query LIKE 'autovacuum%' ;
>>>
>>> query_start | query
>>> | state | pid
>>>
>>> -------------------------------+----------------------------------------------------------------------------------+--------+------
>>> 2013-11-15 07:13:13.281202+00 | autovacuum: VACUUM ANALYZE
>>> public.gamer_device_sightings (to prevent wraparound) | active | 4360
>>>
>>
>>
>