Hi,
(Moving a part of this discussion to hackers)
In #15840 Thierry had the situation that autovacuum apparently could not
keep up, and he ended up with a wraparound situation. Following the
hints and shutting down the cluster and vacuuming the relevant DB in
single user mode did not resolve the issue however. That's because there
was a session with temp tables:
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.
And our logic for dropping temp tables only kicks in autovacuum, but not
in a database manual VACUUM.
Which means that currently the advice we give, namely to shut down and
vacuum the database in singleuser mode plainly doesn't work. Without any
warnings hinting in the right direction.
Do we need to move the orphan temp cleanup code into database vacuums or
such?
Greetings,
Andres Freund