Tom Lane wrote: > Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> 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)
