Re: AutoVacuum Behaviour Question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Bruce McAlister <bruce(dot)mcalister(at)blueface(dot)ie>, General <pgsql-general(at)postgresql(dot)org>, Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: AutoVacuum Behaviour Question
Date: 2007-06-29 02:29:47
Message-ID: 3178.1183084187@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2007-06-29 02:52:31 Re: AutoVacuum Behaviour Question
Previous Message Alvaro Herrera 2007-06-29 00:50:36 Re: AutoVacuum Behaviour Question

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2007-06-29 02:41:14 Re: write past chunk end in ExprContext / to_char
Previous Message Stephen Frost 2007-06-29 02:28:04 Re: 'SET LOCAL ROLE blah;' doesn't work?