RPostgreSQL column names regression

[jonkeane]

I've been testing the development branch of dbplyr and I believe there might be a regression with RPostgreSQL backends: it appears the column names are not being added to the ops of the tbl object so subsequent verbs that reference those columns (select()) don't work.

I believe this is related to removing the special-case db_query_fields.PostgreSQLConnection() called from tbl_sql(). I'm happy to submit a PR that brings back db_query_fields.PostgreSQLConnection() or try a different approach.

test_user = "[redacted]"
test_pw = "[redacted]"

library(dbplyr)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:dbplyr':
#> 
#>     ident, sql
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

# RPostgreSQL
con_rpsql <- DBI::dbConnect(
  RPostgreSQL::PostgreSQL(),
  dbname = "travelling",
  host = "127.0.0.1",
  user = test_user,
  password = test_pw
)
DBI::dbWriteTable(con_rpsql, "mtcars", mtcars)
#> [1] TRUE
DBI::dbDisconnect(con_rpsql)
#> [1] TRUE

con_rpsql <- DBI::dbConnect(
  RPostgreSQL::PostgreSQL(),
  dbname = "travelling",
  host = "127.0.0.1",
  user = test_user,
  password = test_pw
)
str(tbl(con_rpsql, "mtcars"))
#> List of 2
#>  $ src:List of 2
#>   ..$ con  :Formal class 'PostgreSQLConnection' [package "RPostgreSQL"] with 1 slot
#>   .. .. ..@ Id: int [1:2] 92428 1
#>   ..$ disco: NULL
#>   ..- attr(*, "class")= chr [1:4] "src_PostgreSQLConnection" "src_dbi" "src_sql" "src"
#>  $ ops:List of 2
#>   ..$ x   : 'ident' chr "mtcars"
#>   ..$ vars: chr(0) 
#>   ..- attr(*, "class")= chr [1:3] "op_base_remote" "op_base" "op"
#>  - attr(*, "class")= chr [1:5] "tbl_PostgreSQLConnection" "tbl_dbi" "tbl_sql" "tbl_lazy" ...
tbl(con_rpsql, "mtcars") %>%
  select(mpg)
#> Error: Can't subset columns that don't exist.
#> x Column `mpg` doesn't exist.
DBI::dbDisconnect(con_rpsql)
#> [1] TRUE

<sup>Created on 2020-10-08 by the [reprex package](https://reprex.tidyverse.org) (v0.3.0)</sup>

[hadley]

Can you use RPostgres instead?

[jonkeane]

That's what I've done for the vignette that caught this, so {dittodb} is fine without a fix for this.

[hadley]

It probably is too aggressive to just break this though. I think we should just restore the previous method.

[jonkeane]

I'm happy to send a PR that does that.

[hadley]

That would be great, thanks!