Re: AutoVacuum Behaviour Question

Tom Lane wrote:
> 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?)

It's pg_temp_63. Backend 63 is running in another database. It seems
perfectly possible that a backend connects to database A, creates a temp
table, crashes, then connects to database B after restart and then keeps
running there forever :-(

> > 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.

Would it be enough to delete the tuple from pg_class? I guess that will
leave behind the tuples in pg_attribute etc, but I don't see another way
to drop it ... Maybe UPDATE to move it to the local temp schema and
then DROP it?

Or maybe it works to do DROP TABLE pg_temp_63.temp2394 as superuser ...?
I haven't tried.

> 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.

Yes, I was going to suggest that, though it doesn't seem right.

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Tiene valor aquel que admite que es un cobarde" (Fernandel)