Re: AutoVacuum Behaviour Question

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Well, it certainly seems like this shouldn't be happening. Maybe the
> table belonged to a session that crashed, but the pg_class entry has not
> been cleaned up -- possibly because that backend has not connected to
> that particular database.

Hm --- a crash would mean that the temp table would remain until some
other session (a) connected to the same database (b) using the same
BackendId (sinval slot number), and (c) decided to create some temp
tables of its own. So indeed it's not implausible that the table could
hang around for a long time, especially if you were unlucky enough that
the original creator had been using a very high BackendId slot. (Which
pg_temp schema is this table attached to, anyway?)

> Maybe autovacuum itself could do something about cleaning up this kind
> of stuff on sight (--> dropping temp tables belonging to sessions that
> crash). I'm not sure though.

Yeah, we had better investigate some way to clean them up. It was never
obvious before that it mattered to get rid of orphan temp tables, but I
guess it does.

Another possibility is just to ignore temp tables while computing
datvacuumxid. A temp table that survives for > 2G transactions is going
to be trouble, but I'm not sure there's anything we can usefully do
about it anyway --- certainly autovacuum has no power to fix it.

regards, tom lane