dbplyr:::get_col_types.PqConnection doesn't handle schemas

[avsdev-cw]

In short, when using schemas (dbplyr::in_schema) & PostgreSQL, the table name parameter isn't properly built when calling underlying functions. In my case, I'm trying to use the new dbplyr::rows_insert suite of functions.

About as close to a reprex as I can produce:

conn <- DBI::dbConnect(
  RPostgres::Postgres(),
  host = db$host,
  port = db$port,
  dbname = db$name,
  user = db$user,
  password = db$password
)
dplyr::copy_to(conn, mtcars[1:5,], dbplyr::in_schema(db$schema, "cars"), temporary = FALSE)
db_cars <- dplyr::tbl(conn, dbplyr::in_schema(db$schema, "cars"))
new_cars <- mtcars[6:10,]
db_new_cars <- dbplyr::copy_inline(conn, new_cars)
dplyr::rows_insert(db_cars, db_new_cars, conflict = "ignore")

The error I get is:

Error: Expecting a single string value: [type=character; extent=2].
>traceback()
13: stop(structure(list(message = "Expecting a single string value: [type=character; extent=2].", 
        call = NULL, cppstack = NULL), class = c("Rcpp::not_compatible", 
    "C++Error", "error", "condition")))
12: result_create(conn@ptr, statement, immediate)
11: initialize(value, ...)
10: initialize(value, ...)
9: new("PqResult", conn = conn, ptr = result_create(conn@ptr, statement, 
       immediate), sql = statement, bigint = conn@bigint)
8: .local(conn, statement, ...)
7: DBI::dbSendQuery(con, paste0("SELECT * FROM ", name))
6: DBI::dbSendQuery(con, paste0("SELECT * FROM ", name))
5: get_col_types.PqConnection(remote_con(x), name, call)
4: get_col_types(remote_con(x), name, call)
3: rows_auto_copy(x, y, copy = copy)
2: rows_insert.tbl_lazy(db_cars, db_new_cars, conflict = "ignore")
1: dplyr:::rows_insert(db_cars, db_new_cars, conflict = "ignore")

In the function stack I have traced the issue to:

# dbplyr:::get_col_types.PqConnection(remote_con(x), name, call)
function (con, name, call) 
{
    res <- DBI::dbSendQuery(con, paste0("SELECT * FROM ", name))
    on.exit(DBI::dbClearResult(res))
    DBI::dbFetch(res, n = 0)
    col_info_df <- DBI::dbColumnInfo(res)
    set_names(col_info_df[[".typname"]], col_info_df[["name"]])
}

Notice the paste0 call, this effectively creates something akin to the following when a schema is used:

> name <- dbplyr::in_schema("my_schema", "my_table")
> paste0("SELECT * FROM ", name)
[1] "SELECT * FROM my_schema" "SELECT * FROM my_table"

And naturally the error is produced:

> DBI::dbSendQuery(conn, c("SELECT * FROM my_schema", "SELECT * FROM my_table"))
Error: Expecting a single string value: [type=character; extent=2].

I do not know at which point the schema'd name should be built, whether that should be earlier in the stack or not, but I'm fairly sure that it is the cause of the problem.

[avsdev-cw]

Possibly also related to: #938

The same fix would probably work here:

> name <- dbplyr:::remote_name(db_cars)
> con <- dbplyr::remote_con(db_cars)
# as per dbplyr:::get_col_types.PqConnection(con, name, call)
> paste0("SELECT * FROM ", dbplyr::as.sql(name, con))
[1] "SELECT * FROM \"my_schema\".\"cars\""

I'm not sure how many instances of this bug might have been added....

[mgirlich]

Thanks for filing the issue!