Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jan 25, 2018 at 1:14 AM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> * I think temporary tables should not require vacuuming for XID wraparound. Furtherover, should updates/deletes to temporary tables be in-place instead of creating garbage, so that any form of vacuum is unnecessary? Other sessions do not need to read temporary tables.

Temporary tables contain XIDs, so they need to be vacuumed for XID
wraparound. Otherwise, queries against those tables by the session
that created them could yield wrong answers. However, autovacuum
can't perform that vacuuming; it would have to be done by the session.
I think we should consider having backends try to remove their
temporary schema on startup; then, if a temp table in a backend is old
enough that it's due for vacuum for wraparound, have autovacuum kill
the connection. The former is necessary to prevent sessions from
being killed on account of temp tables they "inherited" from a backend
that didn't exit cleanly.

The in-place update idea won't work for a couple of reasons. First, a
command shouldn't see the results of modifications made earlier in the
same command. Second, using cursors, it's possible to have more than
one distinct snapshot open against a temporary table at the same time.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company