Feature request: add a dbResetConnection() generic

[bborgesr]

Sometimes it's useful to reset all user-defined variables back to their defaults in a DBIConnection (set using SET), instead of having to disconnect and fetch a brand new connection. This is particularly important in the context of a connection pool, for which we want to make sure that, when a connection is returned to the pool, its variables will be reset (so that when it's checkout again, it is as good as new).

It seems that this would be very easy to do if DBI had a generic called dbResetConnection() or the like. Each driver would then have to implement the corresponding method for its connections, in order to be considered DBI-compliant. For example, in RMySQL, this could be implemented using MySQL's mysql_reset_connection().

cc @hadley, @jcheng5

[hadley]

What user defined variables are you thinking of?

[bborgesr]

We stumbled upon this when we were trying to fix the encoding situation by running dbSendQuery(conn, "SET NAMES utf8;"). Ideally, this sort of thing would get reset on the call to onPassivate, but there's no DBI mechanism for it at the moment... So, either people never set variables on connections, or if they do, they can never be sure if the next connection they fetch from the pool will be brand new (default settings) or a connection whose settings were previously changed.

[hadley]

I skimmed the postgres and sqlite docs and I couldn't find methods to "reset" a connection. So I'm not sure how we'd solve this in general. We could implement a dbSetParameter() that tracked the settings? But that would still have to assume that you could use a dbGetParameter() to retrieve the previous values.

[bborgesr]

Hmmm, could postgres simply execute a RESET ALL query? sqlite does seem trickier, but this function does at least seem to keep track of the connection params... Could a meantime solution be:

• for driver that can implement this, do;
• for drivers whose DBMS doesn't support a reset function, throw an error/warning when you change some default?

In any case, I don't think this will become a major, major problem (at least for pool). Most Shiny app authors and users won't need to change any defaults. Right now, we have a little note (which will also become explicit in the README) discouraging people from using pool if they need to change a connection's default params...

[hadley]

Would be great to get this in the next version of DBI

[hannes]

We have something like this in the MonetDB driver where the parameters used for dbConnect are kept around in the connection to allow the creation of a secondary connection for checking the status of the currently running query (not ready for prime-time yet). But I would implement a default for this in DBI like this, the parameters to dbConnect are kept and dbConnect is re-executed. In my case, the internal state of the connection is kept in an environment allowing in-place modification, but in the generic case we could have dbResetConnection return the 'reset' connection so that a generic implementation is possible. e.g. con <- dbResetConnection(con)

[hannes]

Also, what about running transactions? Should dbResetConnection abort those or commit?

[hadley]

@hannesmuehleisen that feels more like connection cloning, which is fine for local databases (because it's so fast) but might incur substantial performance penalties otherwise. (The broader context for this issue is the development of a package to manage a connection pool, where efficiently reseting a connection is quite important).

[jcheng5]

@hannesmuehleisen dbResetConnection should definitely rollback transactions. The effect should be analogous to closing the connection and replacing it with a new one, just without the overhead of actually negotiating a new connection.

[krlmlr]

@bborgesr: Is this still relevant?

[krlmlr]

Closing because information is missing and no feedback has been provided. For related problems, please open a new issue and add a link to this issue.