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

From: Andres Freund <andres(at)anarazel(dot)de>
To: Thierry Husson <thusson(at)informiciel(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15840: Vacuum does not work after database stopped for wraparound protection. Database seems unrepearable.
Date: 2019-06-07 21:47:47
Message-ID: 20190607214747.r3icif752clcof6p@alap3.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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.

> 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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Thierry Husson 2019-06-07 22:49:52 Re: BUG #15840: Vacuum does not work after database stopped for wraparound protection. Database seems unrepearable.
Previous Message Thierry Husson 2019-06-07 21:40:27 Re: BUG #15840: Vacuum does not work after database stopped for wraparound protection. Database seems unrepearable.

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2019-06-07 22:41:55 Re: tableam: abstracting relation sizing code
Previous Message Thierry Husson 2019-06-07 21:40:27 Re: BUG #15840: Vacuum does not work after database stopped for wraparound protection. Database seems unrepearable.