Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> 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)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to