Friday, November 27, 2015

RPostgresql : how to pass dynamic parameter to dbGetQuery statement

RPostgresql : R and PostgreSQL Database

Working with RPostgreSQL package

How to pass dynamic / runtime parameter to dbGetQuery in RPostgrSQL ?
#use stri_paste to form a query and pass it into dbGetQuery icd = 'A09'
require(stringi)

qry <- stri_paste("SELECT * FROM visualisation.ipd_disease_datamart WHERE icd ='", icd, "'",collapse="")

rs1 <- dbGetQuery(con, qry)
Error
 
 Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (cannot allocate a new connection -- 
maximum of 16 connections already opened)
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")

con <- dbConnect(drv, dbname="DBName", host="127.0.0.1",port=5432,user="yes",password="yes")
Solution
close the connection. max 16 connection can able to establish from R to PostgreSQL, if exceeds this limit, will throw error.
##list all the connections
dbListConnections(drv)

## Closes the connection
dbDisconnect(con)

## Frees all the resources on the driver
dbUnloadDriver(drv)
#OR on.exit(dbUnloadDriver(drv), add = TRUE)


How to close/drop all the connection Postgresql session.?

We can terminate the PostgreSQL connection using "pg_terminate_backend" SQL command.
In my case I was open up 16 connection using RPostgreSQL unfortunately forget to release them.
So I ended up with Max. connection exceed limit.
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE client_addr = '10.184.36.131' and pid > 20613 AND pid <> pg_backend_pid();
In above query, pg_stat_activity will return list of all the active connection.
I have terminating only the connection from R session which made from the (client_addr) IP 10.184.36.181

No comments: