user connection properties

Hello Gurus,
From which internal tables/views I can get the user connection
application/job name ? (pg_stat _activity has the client ip address only)

Thank you
Isabella-

-----------------------------------------------------------
Isabella A. Ghiurea

Isabella(dot)Ghiurea(at)nrc-cnrc(dot)gc(dot)ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045

Isabella Ghiurea wrote:
> Hello Gurus,
> From which internal tables/views I can get the user connection
> application/job name ? (pg_stat _activity has the client ip address
> only)

I don't think that information is stored anywhere within Postgres.

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

On Tue, Jul 14, 2009 at 4:05 PM, Isabella Ghiurea <
isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> wrote:

> Hello Gurus,
> From which internal tables/views I can get the user connection
> application/job name ? (pg_stat _activity has the client ip address only)

Unlike Oracle, PG doesn't give you the remote application name.

--Scott

Scott Mead <scott(dot)lists(at)enterprisedb(dot)com> wrote:
> Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> wrote:

>> From which internal tables/views I can get the user connection
>> application/job name ? (pg_stat _activity has the client ip
>> address only)

> Unlike Oracle, PG doesn't give you the remote application name.

Sybase ASE and Microsoft SQL Server both give the connecting
application a way to specify an application name, too. When that's
available, it can occasionally help sort out what's what. If we were
to add that, I assumed we'd want to make it optional (for
compatibility).

-Kevin

Hi Kevin,
I'm on with your comments , but I don't think optional is the right
word,.
This information should be collected in one of the internal table,
next to client's host ip and user name . You can get this from JDBC
connection property or any other programing language user connection
properties.This is a "must feature" when you need to know what's been
running in your database server and wish to make some SQL tunning.
BTW: I don't know to much about PG 's " feature wish list " , if we
get more feedback from some of the Gurus we can add this one to the
list.
Isabella

Kevin Grittner wrote:
>
> Scott Mead <scott(dot)lists(at)enterprisedb(dot)com> wrote:
> > Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> wrote:
>
> >> From which internal tables/views I can get the user connection
> >> application/job name ? (pg_stat _activity has the client ip
> >> address only)
>
> > Unlike Oracle, PG doesn't give you the remote application name.
>
> Sybase ASE and Microsoft SQL Server both give the connecting
> application a way to specify an application name, too. When that's
> available, it can occasionally help sort out what's what. If we were
> to add that, I assumed we'd want to make it optional (for
> compatibility).
>
> -Kevin
>

--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella(dot)Ghiurea(at)nrc-cnrc(dot)gc(dot)ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045

Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> wrote:

> I don't think optional is the right word,.

The issue is that the server has no clue what the application is
beyond what the client sends over the wire, so the server must trust
the client to tell it something useful. There is a lot of code out
there which doesn't set this (so far nonexistent) connection property,
and I'm sure that breaking all of that code is not an option. Hence,
if we add it, it will be optional.

> This information should be collected in one of the internal table,
> next to client's host ip and user name .

This would probably show up in pg_stat_activity somewhere.

> BTW: I don't know to much about PG 's " feature wish list " , if
> we get more feedback from some of the Gurus we can add this one
> to the list.

There is a TODO list.

http://wiki.postgresql.org/wiki/Todo

I'll suggest this be added.

-Kevin