Re: pg_stat_activity.application_name

On the admin list there was a request for an application name
column in pg_stat_activity.

http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php

This is available in a lot of other DBMS products, can be useful to
DBAs, and seems pretty cheap and easy. Could we get that onto the
TODO list?

-Kevin

On Thu, Jul 16, 2009 at 2:08 PM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> On the admin list there was a request for an application name
> column in pg_stat_activity.
>
> http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php
>
> This is available in a lot of other DBMS products, can be useful to
> DBAs, and seems pretty cheap and easy.

ah? how do you implement that? and what's the use case for?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> Kevin Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> On the admin list there was a request for an application name
>> column in pg_stat_activity.

> ah? how do you implement that? and what's the use case for?

It would be passed as a connection property. (If that's not feasible,
perhaps a session GUC, which could map to a client-side connection
property is JDBC, etc.) For many environments, it is at least as
important as an IP address or PID to help someone managing a database
with a lot of connections. It would not do anything on the server
except show up in pg_stat_activity as another piece of information
about each connection.

We would probably want to modify psql, pg_dump, etc. to put the
application name into this connection property, at least by default.
We may want to add a command-line switch to allow user override -- to
provide something more detailed. For example,
--application-name='Weekly Purge' could by specified on the psql
command line.

Yes, this is only as useful as the compliance of the client
applications make it.

-Kevin

On Thu, Jul 16, 2009 at 8:08 PM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> On the admin list there was a request for an application name
> column in pg_stat_activity.
>
> http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php
>
> This is available in a lot of other DBMS products, can be useful to
> DBAs, and seems pretty cheap and easy.  Could we get that onto the
> TODO list?

I think you should just add it.

Ok, we probably need some kind of policy for what to do before "just"
adding things to the TODO but I think it should be relatively liberal.
Something like, you should post that you're going to add it to the
-hackers list, get at least one person agreeing with the item and no
fatal flaws. Oh, and you should check for duplicates or for the same
item on the "things we don't want" list. But if having done that you
should assume it's up to you to just go ahead and add it.

In this case I don't see any harm in having an opaque application
identifier. Dangers (but surmountable ones I assume) would be:

1) The authenticity of the application identifier needs to be
downplayed -- don't even think of using it for security for example.

2) encoding issues if different connections are in different encodings...

3) backwards compatibility both in the library api and protocol

--
greg
http://mit.edu/~gsstark/resume.pdf

Greg Stark <gsstark(at)mit(dot)edu> wrote:
> Kevin Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> On the admin list there was a request for an application name
>> column in pg_stat_activity.
>>
>> http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php
>>
>> This is available in a lot of other DBMS products, can be useful to
>> DBAs, and seems pretty cheap and easy. Could we get that onto the
>> TODO list?
>
> I think you should just add it.

Done.

-Kevin

Kevin Grittner wrote:
> We would probably want to modify psql, pg_dump, etc. to put the
> application name into this connection property, at least by default.
> We may want to add a command-line switch to allow user override -- to
> provide something more detailed. For example,
> --application-name='Weekly Purge' could by specified on the psql
> command line.

I've seen it set to argv[0] on other DBMSs, if not set explicitly. That
would be a reasonable default, and would handle psql, pg_dump etc.
without changes.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thursday 16 July 2009 22:08:25 Kevin Grittner wrote:
> On the admin list there was a request for an application name
> column in pg_stat_activity.
>
> http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php
>
> This is available in a lot of other DBMS products, can be useful to
> DBAs, and seems pretty cheap and easy. Could we get that onto the
> TODO list?

A facility to show it in the logs (via log_line_prefix probably) would also be
useful.

Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> A facility to show it in the logs (via log_line_prefix probably)
> would also be useful.

Agreed.

-Kevin

On Fri, Jul 17, 2009 at 9:58 AM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>
>> A facility to show it in the logs (via log_line_prefix probably)
>> would also be useful.
>
> Agreed.
>

ah! that seems a lot more useful. at least, i currently use logs to
identify malformed or bad-performant queries with that i will know
from what webpage they came

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Fri, Jul 17, 2009 at 3:19 AM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
> On Thursday 16 July 2009 22:08:25 Kevin Grittner wrote:
>> On the admin list there was a request for an application name
>> column in pg_stat_activity.
>>
>> http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php
>>
>> This is available in a lot of other DBMS products, can be useful to
>> DBAs, and seems pretty cheap and easy.  Could we get that onto the
>> TODO list?
>
> A facility to show it in the logs (via log_line_prefix probably) would also be
> useful.
>

is there anyone working on this or have plans to work on this? if not,
i will give it a try as soon as this commitfest ends

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Monday 10 August 2009 08:39:17 Jaime Casanova wrote:
> On Fri, Jul 17, 2009 at 3:19 AM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
> > On Thursday 16 July 2009 22:08:25 Kevin Grittner wrote:
> >> On the admin list there was a request for an application name
> >> column in pg_stat_activity.
> >>
> >> http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php
> >>
> >> This is available in a lot of other DBMS products, can be useful to
> >> DBAs, and seems pretty cheap and easy. Could we get that onto the
> >> TODO list?
> >
> > A facility to show it in the logs (via log_line_prefix probably) would
> > also be useful.
>
> is there anyone working on this or have plans to work on this? if not,
> i will give it a try as soon as this commitfest ends

Go for it.