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