Add an application_name for check_pgactivity

[Krysztophe]

Currently check_pgactivity appears in postgresql.log with such lines:

2022-11-30 09:27:42 -04 [31438]: [2-1] db=dbname,user=pgadmin,app=psql,client=[local] STATEMENT:  SELECT d.datname, blks_hit, blks_read

You can often change the application_name one way or another, but I suggest a default one for check_pgactivity, eg:

check_pgactitvity (service: temp_files)

check_pgactitvity - table_bloat

Perhaps adding some info on what is happening inside the service, like parameters? (probably overkill)

If it's ok, I may do the PR.

[frost242]

Hello, You mean by setting the PGAPPNAME env variable before calling psql ?

I'm not against this. However, monitoring should use a dedicated user, non-privileged for current supported PostgreSQL releases. So it's easy to filter them out this way.

[Krysztophe]

However, monitoring should use a dedicated user, non-privileged for current supported PostgreSQL releases. So it's easy to filter them out this way.

You're right, of course. But I don't see it much in the wild. Even in this case, you may have more than one tool to monitor your DB, and use same user for all of them.

I repeat that it could be a way to indicate which service is running.

[frost242]

Yup. I'm fine with your proposal. This feature came with PostgreSQL 9.0 which is now quite old. I'm not sure that there are still older PostgreSQL releases monitored with check_pga.

[rjuju]

I wouldn't be surprised if people have multiple monitoring stuff (say check_pga, prometheus...) and use the same role for both. In any case, having a better application name than "psql" definitely looks like a good idea.

Not sure about having the service too, it seems like it could add some overhead and might end up bloating any application_name monitoring stuff.

Compatibility with pre9.0 should be maintained, so we will likely need to do it with a plan SQL query.

[Krysztophe]

Not sure about having the service too, it seems like it could add some overhead

Overhead? It would be set once for each service.

and might end up bloating any application_name monitoring stuff.

Yes, it would bloat pgbadger reports and similar tools, but we usually filter monitoring queries in these kinds of reports.

An easy work around would be to update application_name only not already set. Define it in the environment or ALTER ROLE if don't want it to change.

Compatibility with pre9.0 should be maintained, so we will likely need to do it with a plan SQL query.

I don't understand. I may miss a bit of history or ignore a trick. I simply imagined to do a SET, if PG ≥ 9.0

[frost242]

I think rjuju meant using SET and not relying on PGAPPNAME - which may leads to produce errors on <9.0 clusters if the client is ≥9.0.

[rjuju]

Overhead? It would be set once for each service.

One or two additional queries are still more overhead than 0 query.

I don't understand. I may miss a bit of history or ignore a trick. I simply imagined to do a SET, if PG ≥ 9.0

Yes, but AFAICS we only retrieve the server version for services that have version requirements (in set_pgversion), so a service like check_connection would have 2 additional queries.

[rjuju]

I think rjuju meant using SET and not relying on PGAPPNAME - which may leads to produce errors on <9.0 clusters if the client is ≥9.0.

Right, but it's likely that it would simply be silently ignored on older versions (I don't think client tools think that PG is a reserved prefix or anything for env variable). But we don't know the server version when launching psql, so we can't rely on env variable unfortunately.

edit: I mean if we want to put anything specific per some db informaiton. I still prefer simply putting "check_pg_activity" as application name.

[ioguix]

But we don't know the server version when launching psql, so we can't rely on env variable unfortunately.

I'm not sure to understand. PGAPPNAME actually appeared in 9.0 with the application_name GUC. It will be ignored by any older backend version.

So, let sets this env variable and forget about it.

I'm +1 on setting application_name to something like check_pgactivity - $service as it might help to quickly find a misbehaving service and disabling it while investigating.

[Krysztophe]

And by the way: any existing application_name was erased and set to psql by psql.