dbplyr/dplyr db_list_tables not working for RPostgreSQL

[JohnMount]

dbplyr/dplyr doesn't seem to support db_list_tables() on PostgreSQL through RPostgreSQL using either the new or old way of building a data source. Submitted here as per dbplyr instructions.

suppressPackageStartupMessages(library('dplyr'))
packageVersion('dbplyr')
#> [1] '1.0.0'
packageVersion('dplyr')
#> [1] '0.7.0'
packageVersion('RPostgreSQL')
#> [1] '0.4.1'

my_db <- dplyr::src_postgres(host = 'localhost',
                             port = 5432,
                             user = 'postgres',
                             password = 'pg')

dplyr::db_list_tables(my_db)
#> Error in UseMethod("db_list_tables"): no applicable method for 'db_list_tables' applied to an object of class "c('src_dbi', 'src_sql', 'src')"

con <- RPostgreSQL::PostgreSQL()
dplyr::db_list_tables(con)
#> Error in UseMethod("db_list_tables"): no applicable method for 'db_list_tables' applied to an object of class "c('PostgreSQLDriver', 'DBIDriver', 'PostgreSQLObject', 'DBIObject', 'dbObjectId')"

[hadley]

I don't think db_list_tables() is no longer necessary, and we'll be deprecating at some point. Instead, use DBI::dbListTables()

[JohnMount]

Would that not make it unnecessarily difficult to code that works generically (i.e. without inspecting class names and versions) with dplyr 0.5.0 and dplyr 0.7.0? Or more urgently: doesn't that break code that worked with dplyr 0.5.0?

It seems like prior to deprecation having db_list_tables() fall back to DBI::dbListTables() would be high value. Also prior to deprecating the method one would expect the method to work (as it is still present). I've direct messaged you so this comment on a currently closed issue has a chance of being seen.

[hadley]

Currently there is only one db_list_tables method:

#' @export
db_list_tables.DBIConnection <- function(con) dbListTables(con)

[JohnMount]

Yes, I get that, but exporting something like the following fixes the problem:

#' @export
db_list_tables.src_dbi <- function(con) DBI::dbListTables(con$con)

Also: I am sorry I accidentally (and wrongly) used a PostgreSQL driver instead of connection in the original example. Below is a working reprex:

suppressPackageStartupMessages(library('dplyr'))
packageVersion('dbplyr')
#> [1] '1.0.0'
packageVersion('dplyr')
#> [1] '0.7.0'
packageVersion('RPostgreSQL')
#> [1] '0.4.1'

my_db <- dplyr::src_postgres(host = 'localhost',
                             port = 5432,
                             user = 'postgres',
                             password = 'pg')

db_list_tables(my_db)
#> Error in UseMethod("db_list_tables"): no applicable method for 'db_list_tables' applied to an object of class "c('src_dbi', 'src_sql', 'src')"

db_list_tables.src_dbi <- function(con) DBI::dbListTables(con$con)

db_list_tables(my_db)
#>  [1] "dR"        "xx"        "mvtr"      "dg"        "ds"       
#>  [6] "res"       "iris"      "d1"        "d2"        "d2b"      
#> [11] "d3"        "d4"        "dlet"      "dcoalesce" "support"  
#> [16] "d"         "dcoal2"    "support2"  "dga"       "mvtc"

con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
                      host = 'localhost',
                      port = 5432,
                      user = 'postgres',
                      password = 'pg')
db_list_tables(con)
#>  [1] "dR"        "xx"        "mvtr"      "dg"        "ds"       
#>  [6] "res"       "iris"      "d1"        "d2"        "d2b"      
#> [11] "d3"        "d4"        "dlet"      "dcoalesce" "support"  
#> [16] "d"         "dcoal2"    "support2"  "dga"       "mvtc"

[hadley]

There are no db_ methods for any dplyr sources:

S3method(db_analyze,DBIConnection)
S3method(db_analyze,MySQLConnection)
S3method(db_begin,DBIConnection)
S3method(db_begin,MySQLConnection)
S3method(db_begin,PostgreSQLConnection)
S3method(db_collect,DBIConnection)
S3method(db_commit,DBIConnection)
S3method(db_commit,MySQLConnection)
S3method(db_compute,DBIConnection)
S3method(db_copy_to,DBIConnection)
S3method(db_create_index,DBIConnection)
S3method(db_create_index,MySQLConnection)
S3method(db_create_indexes,DBIConnection)
S3method(db_create_table,DBIConnection)
S3method(db_data_type,DBIConnection)
S3method(db_data_type,MySQLConnection)
S3method(db_desc,DBIConnection)
S3method(db_desc,MySQLConnection)
S3method(db_desc,OdbcConnection)
S3method(db_desc,PostgreSQLConnection)
S3method(db_desc,SQLiteConnection)
S3method(db_drop_table,DBIConnection)
S3method(db_explain,DBIConnection)
S3method(db_explain,PostgreSQLConnection)
S3method(db_has_table,DBIConnection)
S3method(db_has_table,MySQLConnection)
S3method(db_has_table,PostgreSQLConnection)
S3method(db_insert_into,DBIConnection)
S3method(db_list_tables,DBIConnection)
S3method(db_query_fields,DBIConnection)
S3method(db_query_fields,PostgreSQLConnection)
S3method(db_query_rows,DBIConnection)
S3method(db_rollback,DBIConnection)
S3method(db_rollback,MySQLConnection)
S3method(db_save_query,DBIConnection)
S3method(db_sql_render,DBIConnection)
S3method(db_write_table,DBIConnection)
S3method(db_write_table,MySQLConnection)
S3method(db_write_table,PostgreSQLConnection)

Are you saying that this used to work?

[JohnMount]

Yes, it had been my impression was db_list_tables() used to work with dplyr 0.5.0. I don't have direct notes on running this with RPostgeSQL using dplyr 0.5.0, but it showed up as a behavior change in other code (which is why I was looking into it). Most of my projects have been with sparklyr later, so it looks like I mis-remembered.

But I thought approximate uniformity between data sources was one of the goals.

Right now it does appear to work on at least one dplyr source: sparklyr, so I am not sure of the coverage of your last comment (isn't sparklyr a "db"?).

suppressPackageStartupMessages(library('dplyr'))
packageVersion('dplyr')
#> [1] '0.7.0'
packageVersion('sparklyr')
#> [1] '0.5.6'
my_db <- sparklyr::spark_connect(version='2.0.2', 
                                 master = "local")
db_list_tables(my_db)
#> character(0)

But evidently it doesn't work for dplyr 0.5.0 with RPostgreSQL:

suppressPackageStartupMessages(library('dplyr'))
packageVersion('dplyr')
#> [1] '0.5.0'
packageVersion('RPostgreSQL')
#> [1] '0.4.1'

my_db <- dplyr::src_postgres(host = 'localhost',
                             port = 5432,
                             user = 'postgres',
                             password = 'pg')

dplyr::db_list_tables(my_db)
#> Error in UseMethod("db_list_tables"): no applicable method for 'db_list_tables' applied to an object of class "c('src_postgres', 'src_sql', 'src')"

Obviously this is all in the class differences between a PostgreSQL connection (c("src_dbi" "src_sql" "src"))and sparklyr (c("spark_connection" "spark_shell_connection" "DBIConnection")) connection.

Also, I hadn't been aware that sparklyr 0.5.6 requires dplyr 0.7.0 or above. I had assumed they would keep the dplyr 0.5.0 compatibility longer (especially given things like Sparklyr issue 678).

[hadley]

https://github.com/tidyverse/dplyr/blob/v0.5.0/NAMESPACE suggests that they were never there, so I don't know why your code used to work 😕

[JohnMount]

I am betting I went after the $con slot in more situations than I remember. Sorry for the trouble.