The questions around sqlite3_blob_xxx methods that Roger brought up a couple of months ago are very interesting for me too, and I haven't seen any reply to Roger's message. (Roger - do you have any update?)
As far as I can gather from the cited description of the problem, we should manually acquire SHARED db lock before reading a blob, and RESERVED lock before writing a blob. Can someone confirm that? Also, how blob i/o operations deal with transactional context is indeed not very clear. Based on few words in description of sqlite3_blob_close, I assume blob i/o is transactional, but this also brings a more subtle point: What happens with the cache when a really large BLOB (larger than cache size) is read/written? I guess I should have run some experiments, and I will probably do, but it seems quite probable that everything will be paged out of cache by the blob. Considered that the reason for incremental i/o is (supposedly) to avoid lots of memory allocation, we can expect that every BLOB going through sqlite3_blob_read/write will be quite large, so the cache will be thrashing. Please tell me it is not so :) What I'd suggest is to maybe check whether there is an EXCLUSIVE lock and write directly to filesystem, as when cache spill happens? Otherwise, I'm thinking of creating a separate connection with very small cache specifically to handle blob i/o. Thanks! Igor > -----Original Message----- > From: Roger Binns [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 02, 2007 10:16 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] blob incremental i/o constraints > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > There isn't a documentation or wiki page about the blob i/o > functionality beyond the api reference. I am curious if the behaviour > I > am seeing was intentional. > > Open a blob for reading. Insert into the same table (not affecting the > blob or its row). On calling sqlite3_blob_read, I get SQLITE_ABORT > returned and the errmsg is "not an error". This also happens if I do a > few reads, and then the insert, on the next read. (This also happens > with blobs open for writing and doing read or write after the table is > changed). > > Open a blob for reading. Call sqlite3_blob_write, and get back > SQLITE_READONLY. On next calling sqlite3_blob_close, I also get back > SQLITE_READONLY. If sqlite_blob_close is going to return any errors > from prior reads/writes then it should also do so for the above > paragraph (which it doesn't). > > You can delete (via SQL) a blob that is open. The next > sqlite3_blob_read on the blob gets SQLITE_ABORT. (Heck you can even > change the contents via SQL). > > It isn't stated anywhere what the transactional nature of blob i/o is. > For example is data committed after each write, or does it happen on > the > close. > > Roger > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFHUluYmOOfHg372QQRAr6wAKCyo4lRyfeu5gtAxJ+yfH8/KFhhGwCfTV36 > F5Z1rGEiL8hjdSMIC+XjWTs= > =nd4s > -----END PGP SIGNATURE----- > > ----------------------------------------------------------------------- > ------ > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------- > ------ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users