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

Hi Andres,

Andres Freund <andres(at)anarazel(dot)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