Обсуждение: procpid for temp schema

Hi ,

         is there a way that we could find which procpid or connection that has created a particular temporary  schema (pg_temp_*) ?.

         In one of our db a temporary schema (pg_temp_48) was using more than 200GB of disk space  and how to find the connection which has created the temp schema,  ? 

--
Regards,
Ashokraj

ashok raj <ashokexplorer@gmail.com> writes:
>          is there a way that we could find which procpid or connection that
> has created a particular temporary  schema (pg_temp_*) ?.

In 8.3 and up, the number in the pg_temp_N schema name is the same as
the first component of the virtualxids that that session uses.  Not
sure how much help that is to you, but if the session is actively
doing anything you should be able to identify its number by watching
pg_locks.

            regards, tom lane

ashok raj escribió:
> Hi ,
>
>          is there a way that we could find which procpid or connection that
> has created a particular temporary  schema (pg_temp_*) ?.
>
>          In one of our db a temporary schema (pg_temp_48) was using more
> than 200GB of disk space  and how to find the connection which has created
> the temp schema,  ?

Try this

select backendid, pg_stat_get_backend_pid(backendid) as pid
  from pg_stat_get_backend_idset() as backendid;

Look for backendid=48.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support