Thread: How to get information of active/inactive user connection for postgresql

Hi, group,

Do you know what's the command to get information of active/inactive user connection(session) for postgresql?

Thank you,

Jing

On Wed, 2003-03-19 at 10:59, Flower Sun wrote:
> Do you know what's the command to get information of active/inactive
> user connection(session) for postgresql?

I'm not sure what you mean by an "inactive user connection" -- but
presently connected clients can be viewed using either ps or the pg_stat
system views:

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/monitoring.html

Cheers,

Neil

On Wednesday March 19 2003 8:59, Flower Sun wrote:
>
> Do you know what's the command to get information of active/inactive user
> connection(session) for postgresql?

SELECT pg_stat_get_backend_pid(S.backendid) AS pid, db.datname AS database,
dbu.usename AS user, pg_stat_get_backend_activity(S.backendid) AS
current_query
FROM pg_user dbu, pg_database db, (SELECT pg_stat_get_backend_idset() AS
backendid) AS S,
WHERE db.oid = pg_stat_get_backend_dbid(S.backendid)
  AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid);

Hi, Ed L,

Thank you so much! You gave me the answer as what I expected.

Regards,

Jing

 "Ed L." <pgsql@bluepolka.net> wrote:

On Wednesday March 19 2003 8:59, Flower Sun wrote:
>
> Do you know what's the command to get information of active/inactive user
> connection(session) for postgresql?

SELECT pg_stat_get_backend_pid(S.backendid) AS pid, db.datname AS database,
dbu.usename AS user, pg_stat_get_backend_activity(S.backendid) AS
current_query
FROM pg_user dbu, pg_database db, (SELECT pg_stat_get_backend_idset() AS
backendid) AS S,
WHERE db.oid = pg_stat_get_backend_dbid(S.backendid)
AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid);

On Wednesday March 19 2003 9:59, Flower Sun wrote:
> Hi, Ed L,
> Thank you so much! You gave me the answer as what I expected.
> Regards,
> Jing

Neil Conway's comment reminds me there is a much simpler and more reliable
way:

    select * from pg_stat_activity

I gave you my customized version.

Ed

>  "Ed L." <pgsql@bluepolka.net> wrote:On Wednesday March 19 2003 8:59,
Flower Sun wrote:
> > Do you know what's the command to get information of active/inactive
> > user connection(session) for postgresql?
>
> SELECT pg_stat_get_backend_pid(S.backendid) AS pid, db.datname AS
> database, dbu.usename AS user, pg_stat_get_backend_activity(S.backendid)
> AS current_query
> FROM pg_user dbu, pg_database db, (SELECT pg_stat_get_backend_idset() AS
> backendid) AS S,
> WHERE db.oid = pg_stat_get_backend_dbid(S.backendid)
> AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid);
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!