COPY = TRUE is creating a global temporary table of some kind. Not a session specific temporary table.

[ben1787]

This seems like a bug in the new version of dbplyr.

When I use copy = TRUE, or the underlying copy_to(..., temporary = TRUE) the table is not very temporary. Even if I create a new data connection to the db the table is still there.

First I am posting the reprex using dbplyr 1.4.4. Below that I will paste the reprex with dbplyr 2.0.0:

library(DBI)
library(RPostgreSQL)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql
ch = dbConnect(drv = dbDriver("PostgreSQL"),
                dbname = db,
                host = host,
                port = port,
                user = username,
                password = pwd)

tickers_hedged = data.frame(factset_id='No Hedged Tickers', date = Sys.Date(), instrument_type = as.character(NA))
tickers_hedged_temp_table = paste('tickers_hedged',format(Sys.time(),format = '%Y%m%d%H%M%OS6'),sep='_')
copy_to(dest = ch, df = tickers_hedged, name = tickers_hedged_temp_table, temporary = TRUE)
tbl(ch, tickers_hedged_temp_table)
#> # Source:   table<tickers_hedged_20210120005010.614228> [?? x 3]
#> # Database: postgres 11.0.7
#>   factset_id        date       instrument_type
#>   <chr>             <date>     <chr>          
#> 1 No Hedged Tickers 2021-01-20 <NA>

dbDisconnect(ch)
#> [1] TRUE


ch2 = dbConnect(drv = dbDriver("PostgreSQL"),
                dbname = db,
                host = host,
                port = port,
                user = username,
                password = pwd)
tbl(ch2, tickers_hedged_temp_table)
#> Error in postgresqlExecStatement(conn, statement, ...): RS-DBI driver: (could not Retrieve the result : ERROR:  relation "tickers_hedged_20210120005010.614228" does not exist
#> LINE 1: SELECT * FROM "tickers_hedged_20210120005010.614228" AS "zzz...
#>                       ^
#> )
#expected behavior

^{Created on 2021-01-20 by the reprex package (v0.3.0)}

Now for dbplyr 2.0.0:

library(DBI)
library(RPostgreSQL)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql
ch = dbConnect(drv = dbDriver("PostgreSQL"),
                dbname = db,
                host = host,
                port = port,
                user = username,
                password = pwd)

tickers_hedged = data.frame(factset_id='No Hedged Tickers', date = Sys.Date(), instrument_type = as.character(NA))
tickers_hedged_temp_table = paste('tickers_hedged',format(Sys.time(),format = '%Y%m%d%H%M%OS6'),sep='_')
copy_to(dest = ch, df = tickers_hedged, name = tickers_hedged_temp_table, temporary = TRUE)
tbl(ch, tickers_hedged_temp_table)
#> # Source:   table<tickers_hedged_20210120005131.538403> [?? x 3]
#> # Database: postgres 11.0.7
#>   factset_id        date       instrument_type
#>   <chr>             <date>     <chr>          
#> 1 No Hedged Tickers 2021-01-20 <NA>

dbDisconnect(ch)
#> [1] TRUE

ch2 = dbConnect(drv = dbDriver("PostgreSQL"),
                dbname = db,
                host = host,
                port = port,
                user = username,
                password = pwd)
tbl(ch2, tickers_hedged_temp_table)
#> # Source:   table<tickers_hedged_20210120005131.538403> [?? x 3]
#> # Database: postgres 11.0.7
#>   factset_id        date       instrument_type
#>   <chr>             <date>     <chr>          
#> 1 No Hedged Tickers 2021-01-20 <NA>
#this should fail right? but it does not, it finds the table still

^{Created on 2021-01-20 by the reprex package (v0.3.0)}

[ben1787]

Using Postgres btw.

[hadley]

Can you please provide a minimal reprex (reproducible example)? The goal of a reprex is to make it as easy as possible for me to recreate your problem so that I can fix it: please help me help you!

If you've never heard of a reprex before, start by reading "What is a reprex", and follow the advice further down the page. Please make sure your reprex is created with the reprex package as it gives nicely formatted output and avoids a number of common pitfalls.

[ben1787]

Hi Hadley. The "what is reprex" link you sent doesn't work. However I did create a very straightforward reproduceable example above. Is it unclear?

[hadley]

@ben1787 try this one — https://reprex.tidyverse.org/articles/reprex-dos-and-donts.html. And see http://dbplyr.tidyverse.org/articles/reprex.html for dbplyr specific advice.

[ben1787]

Thanks Hadley. Looks like this will be tough to reprex because I need to actually make a db connection to postgres and make a temp table. On the other hand, if there is a way I can just see the sql that copy_to is creating that would help me debug. Unfortunately I can't just pipe it into a sql render because other things are going on under the hood there.

[hadley]

Even if the reprex doesn't work on my computer, it would still be useful to see exactly what you are doing because there are at least three ways to connect to a postgres database, and it matters which one you use.

[ben1787]

Ah sure of course let me add that.

Basically my db is just filling up with these dbplyr_XXX tables.

[ben1787]

Added.

[hadley]

Oops, closed the wrong issue.

[hadley]

As I suspected, it's not a problem for RPostgres, but is for RPostgreSQL:

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

# RPostgres --------------------------------------------------------------------
con1 <- DBI::dbConnect(RPostgres::Postgres(), dbname = "test")
con2 <- DBI::dbConnect(RPostgres::Postgres(), dbname = "test")

copy_to(con1, data.frame(x = 1), "df")
DBI::dbListTables(con1)
#> [1] "df"
DBI::dbListTables(con2)
#> character(0)

# RPostgresSQL ------------------------------------------------------------------
con1 <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), dbname = "test")
con2 <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), dbname = "test")

copy_to(con1, data.frame(x = 1), "df")
DBI::dbListTables(con1)
#> [1] "df" "df"
DBI::dbListTables(con2)
#> [1] "df" "df"

DBI::dbRemoveTable(con1, "df")
#> [1] TRUE

^{Created on 2021-01-21 by the reprex package (v0.3.0.9001)}

[hadley]

The problem is that in dbplyr 2.0.0:

• db_write_table() now calls DBI::dbWriteTable() instead of nine generics
  that formerly each did a small part: db_create_indexes(), db_begin(),
  db_rollback(), db_commit(), db_list_tables(), drop_drop_table(),
  db_has_table(), db_create_table(), and db_data_types(). You can
  now delete the methods for these generics.

This significantly reduces the complexity of dbplyr, but unfortunately RPostgresSQL doesn't support the temporary argument. I'm not sure how to work around this without a bunch of work.

One simple option would be to make temporary = TRUE throw an error; that way you at least know what's going wrong.