4

I know that PostgreSQL database allows to keep track of application name for each connection and it looks like application_name variable should work with RPostgreSQL, but how exactly should I do this? Adding variable application_name = "test" to dbConnect doesn't work.

kismsu
  • 1,049
  • 7
  • 22
  • application_name does not appear to be a parameter that you can set when connecting to a database: http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS – hadley Feb 18 '15 at 03:53
  • You mean in RPostgreSQL? – kismsu Feb 18 '15 at 16:46
  • @hadley, 'application_name' appears in the page that you linked, it is definitely a parameter that you can set in PostgreSQL, as you can see [here](http://stackoverflow.com/questions/15685861/setting-application-name-on-postgres-sqlalchemy) – ncocacola Feb 26 '15 at 16:30
  • @ncocacola ok must've missed that. You can set in `dbConnect()` in https://github.com/rstats-db/RPostgres – hadley Feb 26 '15 at 19:30
  • Great, thanks. @hadley just to check with you, are you planning to replace RPostgreSQL by RPostgres in dplyr? – kismsu Feb 27 '15 at 08:19
  • @kismsu yes, eventually – hadley Feb 27 '15 at 16:01

2 Answers2

3

I'm not sure you can pass application_name='test' as an argument to dbConnect in RPostgreSQL (there is an optional options argument, but I couldn't figure out what kind of data it expects).

An alternative would be to run the following SQL query immediately after opening the connection:

SET application_name='test'

and it should work until you close the connection.

ncocacola
  • 485
  • 3
  • 9
1

It's not well documented but something like this works:

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, 
           dbname  = "<my_db>",
           host = "<my_host>", 
           [...], 
           options = "-c application_name=my_app_name"
       )

It works with dbPool too (package 'pool'):

pool <- dbPool(
            drv = dbDriver("PostgreSQL"),
            dbname = "<my_db>",
            host = "<my_host>",
            [...]
            minSize = 0,
            maxSize = 3,
            options = "-c application_name=my_app_name"
        )

Check with pg_stat_activity

mydb=> SELECT state, usename, application_name FROM pg_stat_activity WHERE datname = 'mydb';
 state  | usename  | application_name
--------+----------+------------------
 active | postgres | psql
 idle   | myuser    | my_app_name
fxi
  • 607
  • 8
  • 16