Waiting for 8.5 – Application name reporting

On 28th of November Tom Lane committed patch by Dave Page, which adds new capability to system monitoring and logging:

Log Message:
-----------
Add support for an application_name parameter, which is displayed in
pg_stat_activity and recorded in log entries.
 
Dave Page, reviewed by Andres Freund

Description, is pretty simple, but let's see how it can be used.

First, let's modify postgresql.conf to get application_name in logs:

log_line_prefix = '%m %u@%d %p %r %a : '

And now, let's try it:

=> psql -c 'select 1'; tail -n 2 DATA/pg_log/postgresql-2009-12-29_150030.log
 ?COLUMN?
----------
        1
(1 ROW)
 
2009-12-29 15:09:03.064 CET pgdba@pgdba 28484 [LOCAL] [UNKNOWN] : LOG:  duration: 0.167 ms  statement: SELECT 1
2009-12-29 15:09:03.066 CET pgdba@pgdba 28484 [LOCAL] [UNKNOWN] : LOG:  disconnection: SESSION TIME: 0:00:00.003 USER=pgdba DATABASE=pgdba host=[LOCAL]

Hmm. App name is unknown. This is because you have to explictly set application name. How? it's relatively simple. Let's try it in couple of ways:

=> export PGAPPNAME="From_Env"
 
=> psql
# SELECT 'app name test #1';
     ?COLUMN?
------------------
 app name test #1
(1 ROW)
 
# SET application_name = 'From SQL itself';
SET
 
# SELECT 'app name test #2';
     ?COLUMN?
------------------
 app name test #2
(1 ROW)

And now, let's check the logs:

=> grep 'app name test' DATA/pg_log/postgresql-2009-12-29_150030.log
2009-12-29 15:10:14.608 CET pgdba@pgdba 30407 [LOCAL] From_Env : LOG:  duration: 0.213 ms  statement: SELECT 'app name test #1';
2009-12-29 15:10:30.088 CET pgdba@pgdba 30407 [LOCAL] FROM SQL itself : LOG:  duration: 0.081 ms  statement: SELECT 'app name test #2';

Sweet. Names got logged.

Here comes a warning – application_name is limited to 64 names (NAMEDATALEN from sources, so you can change this limit by recompiling PostgreSQL), and can contain only standard printable characters – anything else will be converted to “?":

=> export PGAPPNAME="Zażółć Gęślą Jaźń"
 
=> psql -qAt -c "select application_name from pg_stat_activity where procpid = pg_backend_pid()"
Za???????? G????l?? Ja????

Please notice, that number of question marks suggests that PostgreSQL treats application_name as ascii string, even when given UTF8 – which is not bad given it's limitation to only standard printable characters, but it's worth notting.

Now, because it supports environment variable, you can start using it without any change in your code.

Let's imagine you have following program in Perl:

#!/usr/bin/perl
USE strict;
USE warnings;
USE DBI;
 
my $dbh = DBI->CONNECT( 'dbi:Pg:dbname=depesz;host=127.0.0.1;port=5850' );
 
my $sth = $dbh->PREPARE( 'SELECT * FROM pg_stat_activity WHERE procpid = pg_backend_pid()' );
$sth->EXECUTE();
my $row = $sth->fetchrow_hashref();
$sth->finish;
 
$dbh->disconnect;
 
FOR my $key ( sort KEYS %{ $row } ) {
    printf "%-20s : %s\n", $key, $row->{ $key };
}
 
exit;

When I run it it shows:

=$ perl test.pl
application_name     :
backend_start        : 2009-12-29 15:21:48.694813+01
client_addr          : 127.0.0.1
client_port          : 53700
current_query        : SELECT * FROM pg_stat_activity WHERE procpid = pg_backend_pid()
datid                : 16397
datname              : depesz
procpid              : 21358
query_start          : 2009-12-29 15:21:48.697376+01
usename              : depesz
usesysid             : 16385
waiting              : 0
xact_start           : 2009-12-29 15:21:48.697287+01

Now, without any change in code, I can:

=$ PGAPPNAME="test it" perl test.pl
application_name     : test it
backend_start        : 2009-12-29 15:25:23.598965+01
client_addr          : 127.0.0.1
client_port          : 53718
current_query        : SELECT * FROM pg_stat_activity WHERE procpid = pg_backend_pid()
datid                : 16397
datname              : depesz
procpid              : 28751
query_start          : 2009-12-29 15:25:23.601487+01
usename              : depesz
usesysid             : 16385
waiting              : 0
xact_start           : 2009-12-29 15:25:23.601398+01

Pretty sweet. On the other hand, I think that developers should solve it in more general way – by adding ability to output any GUC to logs, and perhaps only setting one special to be displayed in pg_stat_activity.

10 thoughts on “Waiting for 8.5 – Application name reporting”

  2. any way to set it from within perl? (besides the obvious running of a query that runs a SET).

    are there any features involving application_name for Access Control? e.g. deny access if application_name is not valid.

  3. It can’t be used a security feature, as the client lie about it and it can be SET it any time, That’s worth keeping in mind also if you use it in log_line_prefix.

  4. @Caleb sure you can: perl -MDBI -MData::Dumper -e ‘$ENV{“PGAPPNAME”}=”depesz test”;print Dumper(DBI->connect(“dbi:Pg:dbname=depesz”)->selectall_arrayref(“SELECT * from pg_stat_activity where procpid = pg_backend_pid()”))’

  5. Can this be defined when connecting via JDBC as well?
    e.g. specifying a parameter to the URL?

  7. @Heikki I don’t that it isn’t already a security feature… if I set it and I see stuff pop up in my logs that doesn’t have a correct string… I have a problem. I’m not saying it could be a be all to end all feature… but you can spoof IP’s too and yet hba still uses those… just saying almost every level of security can be bypassed. That’s why we have multiple layers.

  9. Do you know if this will work inside PL/pgSQL ?
    I need a way to find out where in function execution currently hungs. Without going through log files that is. Setting application_name after every SQL call in a function to something like “myFunc.Step1” would help me a lot.

Comments are closed.