How do i fix the warning message "Closing open result set, cancelling previous query" when querying a PostgreSQL database in R?

Below is a snippet of my code that I use in R to extract IDs from a PostgreSQL database. When I run the function I get the following warning message from R:

In result_create(conn@ptr, statement) : Closing open result set, cancelling previous query

How do I avoid this warning message from happening without making use of options(warn=-1) at the beginning of my code, suppressing the warning instead of

con <- dbConnect(RPostgres::Postgres(),
                 user = "postgres",
                 dbname  = "DataBaseName",
                 password  = "123456",
                 port  = 5431)


get_id <- function(connection, table){

  query <- toString(paste("SELECT id FROM ", table, sep = ""))
  data_extract_query <- dbSendQuery(connection, query)
  data_extract <- dbFetch(data_extract_query)
  return(data_extract)
}


get_id(con, "users")

Solution

I found a method for solving the problem.

I found a thread on GitHub for RSQLite a https://github.com/r-dbi/RSQLite/issues/143. In this thread, they explicitly set n = -1 in the dbFetch() function.

This seemed to solve my problem, and the warning message did not show up again by editing the code like the following:

data_extract <- dbFetch(data_extract_query, n = -1)

The meaning of n is the number of rows that the query should return. By setting this to -1 all rows will be retrieved. By default, it is set to n = -1 but for some reason, in this build of R (3.6.3) the warning will still be shown.

Calling ?dbFetch in R you can see more information on this. I have included a snippet from the R-help page:

Usage

dbFetch(res, n = -1, ...)

fetch(res, n = -1, ...)

Arguments

res An object inheriting from DBIResult, created by dbSendQuery().

n maximum number of records to retrieve per fetch. Use n = -1 or n = Inf to retrieve all pending records. Some implementations may recognize other special values.

... Other arguments passed on to methods.