From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
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:52:31 |
Message-ID: | 20070629025230.GQ32626@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-06-29 03:07:27 | Re: AutoVacuum Behaviour Question |
Previous Message | Tom Lane | 2007-06-29 02:29:47 | Re: AutoVacuum Behaviour Question |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-06-29 03:07:27 | Re: AutoVacuum Behaviour Question |
Previous Message | Tom Lane | 2007-06-29 02:49:20 | Re: 'SET LOCAL ROLE blah;' doesn't work? |