[R-sig-DB] RSQLite "close resultSet before continuing" error in dbBeginTransaction()

[Andrew Piskorski]

On Fri, Aug 19, 2011 at 09:14:41PM -0700, Seth Falcon wrote:

> It's pretty hard to provide much guidance without more detail on the
> code you are actually running.  Is there any chance you can provide a
> reproducible example or show some version of the code you are running?

Well, while reproducing the segfault problem that I just (separately)
emailed about, I discovered that I can also use that simpler case to
trigger a "close resultSet before continuing" error.

For this, do the bad update statement (see other email) once.  This
first time, rather than segfaulting it will problably throw this
error:

  > dbGetPreparedQuery(handle ,update.sql ,bind.data=bind.df) 
  Error in sqliteExecStatement(con, statement, bind.data) :  
    STRING_ELT() can only be applied to a 'character vector', not a 'NULL' 
 
At that point, rather than running the bad update statement several more
times to get the segfault, instead do a query, which fails like so:

  > dbGetQuery(handle ,"select count(*) from atp_1") 
  Error in sqliteExecStatement(con, statement, bind.data) :  
    RS-DBI driver: (connection with pending rows, close resultSet before continuing) 

Note that my previous encounter with this error was while was while
doing insert statements not updates, and I never saw that
"STRING_ELT() can only be applied" error then.  However, some of those
inserts probably failed due to integrity constraints on my SQLite
table, so to hand-wave a bit, perhaps that left the RSQLite connection
in a similar "pending rows" state.

Btw, so far, explicitly calling dbClearResult() right before
dbBeginTransaction() seems to prevent dbBeginTransaction() from ever
failing with that "close resultSet before continuing" error.

> Can you provide an outline of what you mean by "running background
> jobs"?  Do you have more than one process inserting into the same
> SQLite database?

Yes, I used Sun Grid Engine to start up 5 or 6 R processes at once,
with each R process writing to the same SQLite database over NFS.
(Which mostly seems to work well!)

However, for my simpler example above, I used just one R process, and
a newly created empty SQLite database on a local disk - no NFS and no
concurrency at all.  I think I forgot to mention it, but the segfault
from my other email also occurred in the strictly single process local
disk case.

-- 
Andrew Piskorski <atp using piskorski.com>
http://www.piskorski.com/