Passing postgresql parameter keywords via RPostgreSQL

Question

I would like to pass some parameter keywords (specifically keepalives, keepalives_idle, keepalives_count , or connect_timeout) using the R package RPostgreQSL to connect to my DB. I have searched the docs and stackoverflow for an implementation, but haven't found a solution.

Is there a way to pass these parameter keywords using RPostgresSQL?

What I have tried:

1. Through the dbConnect function as additional arguments

conn <- RPostgreSQL::dbConnect(driver,
                               host = Sys.getenv("DBHOST"),
                               dbname = Sys.getenv("DBNAME"),
                               user = Sys.getenv("DBUSER"),
                               password = Sys.getenv("DBPASSWORD"),
                               port = Sys.getenv("DBPORT"),
                               # Parameter Keywords
                               connect_timeout = 2, 
                               keepalives = 0,
                               keepalives_idle = 0, 
                               keepalives_count = 1, 

)

This results in:

Error in postgresqlNewConnection(drv, ...) : 
  unused arguments (connect_timeout = 2, keepalives = 0, keepalives_idle = 0, keepalives_count = 1)

2. As additional command line options:

conn <- RPostgreSQL::dbConnect(driver,
                               host = Sys.getenv("DBHOST"),
                               dbname = Sys.getenv("DBNAME"),
                               user = Sys.getenv("DBUSER"),
                               password = Sys.getenv("DBPASSWORD"),
                               port = Sys.getenv("DBPORT"),
                               options = "-c connect_timeout=2"
)

This results in:

Error in postgresqlNewConnection(drv, ...) : 
  RPosgreSQL error: could not connect <omitted>@<omitted>:<omitted> on dbname "<omitted>" FATAL:  unrecognized configuration parameter "connect_timeout"

3. Through the dbSendQuery function:

RPostgreSQL::dbSendQuery(conn, "SET statement_timeout to 1; SET connect_timeout to 2 ")

This results in

Error in postgresqlExecStatement(conn, statement, ...) : 
  RPosgreSQL error: could not Retrieve the result : ERROR:  unrecognized configuration parameter "connect_timeout"

Answer 1

TL;DR: I think RPostgreQSL does not support these connection parameters right now.

The R doc for dbConnect-methods says

options Command-line options to be sent to the server.

• I think it correspond to the options parameter in the list of parameter keywords:

options Specifies command-line options to send to the server at connection start. For example, setting this to -c geqo=off sets the session's value of the geqo parameter to off. Spaces within this string are considered to separate command-line arguments, unless escaped with a backslash (); write \ to represent a literal backslash. For a detailed discussion of the available options, consult Chapter 20.

• I tried a few options from Chapter 20, and the server does accept these parameters. E.g.

    conn <- RPostgreSQL::dbConnect(driver,
                                   host = Sys.getenv("DBHOST"),
                                   dbname = Sys.getenv("DBNAME"),
                                   user = Sys.getenv("DBUSER"),                               
                                   password = Sys.getenv("DBPASSWORD"),
                                   port = Sys.getenv("DBPORT"),
                                   options="-c client_min_messages=error"
    )

But the parameters you want to use, e.g. connect_timeout, are not part of chapter 20 (server options). Instead, they are connection parameters parallel to the options parameter. My hunch is that RPostgreQSL does not support them right now.