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:

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)

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"

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"

Sheng Pei Wang · Accepted Answer · 2023-08-09 19:51:21Z

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.

answered Aug 9, 2023 at 19:51

Sheng Pei Wang

261 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Great answer, thanks! Confirming I'm seeing the same behaviour with Chapter 20 args working, but not those listed in the parameter keywords with the exception of options. I found this R package that does support passing arguments from the list of parameter keywords directly to the dbConnect call. github.com/r-dbi/RPostgres

Collectives™ on Stack Overflow

Passing postgresql parameter keywords via RPostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related