pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedprocpid for temp schema
3+ messages / 3 participants
[nested] [flat]
* procpid for temp schema
@ 2009-03-31 08:00 ashok raj <ashokexplorer@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: ashok raj @ 2009-03-31 08:00 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: procpid for temp schema
@ 2009-03-31 14:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: ashok raj <ashokexplorer@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2009-03-31 14:07 UTC (permalink / raw)
To: ashok raj <ashokexplorer@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: procpid for temp schema
@ 2009-03-31 14:34 Alvaro Herrera <alvherre@commandprompt.com>
parent: ashok raj <ashokexplorer@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2009-03-31 14:34 UTC (permalink / raw)
To: ashok raj <ashokexplorer@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-03-31 14:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-31 08:00 procpid for temp schema ashok raj <ashokexplorer@gmail.com>
2009-03-31 14:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-31 14:34 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox