Re: AutoVacuum Behaviour Question

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.