Name collision in compute(temporary = FALSE)

[bairdj]

By default, calling compute creates a table with name "dbplyr_n". Although temporary tables are isolated between sessions, if a user calls temporary = FALSE without explicitly setting a name, then the table is persisted using the dbplyr_ name. Other users using dbplyr on the same database can then experience collisions when calling compute as the generated table name already exists. Best practice would be to avoid persisting these temp tables without specifying a name, but it can happen.

Possible solutions:

1. Allow user to set an option to replace the default "dbplyr_" prefix with something unique to them
2. Don't allow user to set temporary = FALSE without specifying a table name
3. Check if generated table name exists before trying to create it, and create another one if not
4. Use a suffix of random letters instead of sequential numbers

Option 1 relies on the user to set the option, and they could still potentially collide with their own tables.
Option 2 should stop the problem at the root, but would be a breaking change.
Option 3 is undesirable IMO due to extra call to DB, and theoretically could result in many calls if all generated names were taken.
Option 4 would be a reasonable solution and miniscule probability of collisions

[mgirlich]

I prefer option 2, even though it is a breaking change.
@hadley What's your opinion here/

[hadley]

Most temporary tables are session specific. What databases is in use here?

[mgirlich]

@hadley The title is misleading here. As I understand it, the issue is about compute(temporary = FALSE).

[hadley]

Oooh, got it. In that case, option two sounds right to me. To avoid breaking existing code, we could start by making this a warning, rather than an error.