Re: BUG #15840: Vacuum does not work after database stopped forwraparound protection. Database seems unrepearable.

Hi Andres,

Andres Freund <andres@anarazel.de> a écrit :

> Hi,
>
> On 2019-06-07 16:40:27 -0500, Thierry Husson wrote:
>>   oid   |                 oid                  | relkind | relfrozenxid |
>> age
>> --------+--------------------------------------+---------+--------------+------------
>>  460564 | pg_temp_3.cur_semt700_progsync_4996  | r       |        36464 |
>> 2146483652
>>  460764 | pg_temp_8.cur_semt700_progsync_5568  | r       |     19836544 |
>> 2126683572
>>  460718 | pg_temp_4.cur_semt700_progsync_5564  | r       |     19836544 |
>> 2126683572
>>  460721 | pg_temp_5.cur_semt700_progsync_5565  | r       |     19836544 |
>> 2126683572
>>  461068 | pg_temp_22.cur_semt700_progsync_5581 | r       |     19836544 |
>> 2126683572
>>
>> These are temporary tables to manage concurrency & server load. It seems the
>> sudden disconnection due to wraparound protection didn't get them removed. I
>> removed them manually under single mode and there is no more warning now,
>> vacuum command included. Your command is very interesting to know.
>
> Hm. But you do have autovacuum enabled, is that right?  If enabled, have
> you tuned it at all? It seems quite possible that given your load (10
> parallel loads), the default settings werent aggressive enough.

Yes autovacuum is enabled. Aggressiveness was effectively a recent  
problem I had and putting its max_worker to 8 wasn't a solution, there  
were all busy 24/7 and I had to do a daily script to help it. The  
solution was to push vacuum_cost_limit to 2000, since then it works  
like a charm. Another issue was autovaccuums were taking the lock over  
my running vacuums, making them waiting for 5 days instead of taking  
around 1 hour. I could do another post on that but it's not PG12  
specific, I have it with 10.x

>> It annoying PG create a xId for empty temporary tables. You can't clear it
>> with a vacuum as there is no record. I have to terminate connexions of my
>> deamon processes daily to avoid wraparound protection. Is there a way to
>> tell PG to forget these tables on its age estimation?
>
> Normally postgres would drop such "orphaned" temp tables on its own, in
> autovacuum (triggering it when close to a wraparound, even if
> disabled). But if it can't keep up for some reason, then that's not
> necessarily good enough with very rapid xid usage as you seem to have.
>
> I'll start a thread about this subtopic on -hackers.
> Greetings,
>
> Andres Freund

What is the link to this forum? I'm very very interested to follow  
that subtopic & I could make some tests if necessary.

Have a great weekend & thanks for your time :)

Thierry