RPostgresql : how to pass dynamic parameter to dbGetQuery statement

previous post - Working with Association in R

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

Next : Apache Spark : start Worker across cluster