Skip site navigation (1) Skip section navigation (2)

Re: AutoVacuum Behaviour Question

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Bruce McAlister <bruce(dot)mcalister(at)blueface(dot)ie>
Cc: General <pgsql-general(at)postgresql(dot)org>, Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: AutoVacuum Behaviour Question
Date: 2007-06-29 00:50:36
Message-ID: 20070629005036.GE32626@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Bruce, please make sure to keep the list copied on replies.  I think
there is an important bug here and I don't want it to get lost just
because I lose track of it.  I'm also crossposting to pgsql-hackers.

Bruce McAlister wrote:

> okidoki, I tried this:
> 
> blueface-crm=# select relname, nspname from pg_class join pg_namespace
> on (relnamespace = pg_namespace.oid) where
> pg_is_other_temp_schema(relnamespace);
>  relname  |  nspname
> ----------+------------
>  temp4295 | pg_temp_63
> (1 row)
> 
> blueface-crm=# select pg_stat_get_backend_pid(63);
>  pg_stat_get_backend_pid
> -------------------------
>                     6661
> (1 row)
> 
> blueface-crm=# select datname, client_addr, client_port from
> pg_stat_activity where procpid = '6661';
>     datname     | client_addr | client_port
> ----------------+-------------+-------------
>  whitelabel-ibb | 10.6.0.181  |        1587
> (1 row)
> 
> Is that correct? If it is then I'm really confused, how can a connection
> to the whitelabel-ibb database create temporary tables in the
> blueface-crm database?

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.

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.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-06-29 01:07:50
Subject: Re: How do we create the releases?
Previous:From: Tom LaneDate: 2007-06-29 00:34:05
Subject: Re: lazy vacuum sleeps with exclusive lock on table

pgsql-general by date

Next:From: Tom LaneDate: 2007-06-29 02:29:47
Subject: Re: AutoVacuum Behaviour Question
Previous:From: Michael GlaesemannDate: 2007-06-28 21:28:51
Subject: Re: date time function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group