[R-sig-DB] RSQLite: ATTACH statement not executed when the db connection is holding a resultSet

[Seth Falcon]

Herve Pages <hpages using fhcrc.org> writes:
>> I'm not sure what you mean.  What do you expect dbGetQuery to report?
>> 
>> The above looks "right" to me.  You got a warning that told you that
>> your ATTACH occured on a temp connection.
>
> Are you sure? My understanding is that the ATTACH didn't occur on a temp connection
> but really occurred on the db2 connection itself (I mean the real one). It's my
> dbGetQuery(db2, "SELECT * FROM db1.t1") that was sent later thru a temporary connection
> and thus was not aware that db1 was attached to db2. Or am I missing
> something?

Sorry, I was not being careful.  You are right, the ATTACH should have
occured on the db2 connection, but because you have an incomplete
result set hanging around, the subsequent calls to dbGetQuery are
executed on a connection without the attach -- so you are not missing
something and I was.  {though I'm still missing what you expected, the
output looks "right"}.

> I agree. But the ATTACH could have been sent a long time before (many queries before
> in the query history), and not necessarily by me, so I don't really know the who/how/when
> of it. And then suddenly, db1 doesn't seem to be attached to db2 anymore, even if everything
> so far seemed to indicate that it was attached (let's say I've sent
> dbGetQuery(db2, "SELECT * FROM db1.t1") many times before with no problems because there
> was no pending results in db2).
>
> So we have a situation where depending on whether I use dbGetQuery() or dbSendQuery() and
> whether there is a pending result set or not, db1 will sometimes appear as attached to db2,
> and sometimes not. That's what I mean by dbGetQuery() not reporting
> what one would expect.

And that's why as a start, I added the warning message which
presumably would lead you to the problem rather quickly.

> Maybe the motivation behind the temporary connection "feature" feature was to provide
> some convenience to the user so s/he can do:
>
>   dbGetQuery(db2, myquery)
>
> even if there is a pending result set. But then it should not affect the semantic of
> dbGetQuery() i.e. one might expect the same result as with:
>
>   ## clear all pending results first
>   while (length(dbListResults(db2)) >= 1) dbClearResult(dbListResults(db2)[[1]])

why not:

    lapply(dbListResults(db2), dbClearResult)

>   ## send the query
>   dbGetQuery(db2, myquery)
>
> which is not the case.
>
> I understand that this is due to the temporary connection "feature" and that it might
> look right to anybody who knows all the internal gears, but still...
>
> BTW, according to the documentation it seems that the 'cache_size' and 'synchronous'
> values that the user can specify when opening an SQLite connection with dbConnect()
> are not used for the temporary connection. Any reason behind this?

Only that I only learned of the temp connection behavior from your
detailed bug report ;-)

I'm becomming quite convinced that the temp conneciton "feature" is
far too expensive in terms of maintenance (the cache_size issue) and
user confusion (the ATTACH type of issues).  I would not be surprised if
the code originated before SQLite even supported ATTACH.

I will be testing a patch that removes the temp connection and gives a
clear error message about how to close open connections.

Thanks, Herve, for your detailed explanations and report.

+ seth

-- 
Seth Falcon | seth using userprimary.net | blog: http://userprimary.net/user/