[sqlite] two threads block eachother opening db (WAL)
I have found that when my process has a lot of threads each of which opens a DIFFERENT database, they each block on eachother while opening each database. Here is a little rust program to demonstrate the problem: If you simply remove the line "PRAGMA journal_mode = WAL;", then the program runs more than 5x faster. This is at least on conflict with the documentation, as the documentation suggests that a thread is a process for the purposes of sqlite's concurrency, at least when OPEN_NO_MUTEX is specified. I also feel like it's a bug in general, as opening two unrelated databases should not block eachother. Each thread blocks at this point: #0 __lll_lock_wait () at ../sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135 #1 0x774e4b95 in __GI___pthread_mutex_lock (mutex=0x77dd8148 ) at ../nptl/pthread_mutex_lock.c:80 #2 0x77b26419 in unixEnterMutex () at sqlite3.c:31952 #3 unixLock (id=0x74a25180, eFileLock=1) at sqlite3.c:32894 #4 0x77b1f2fa in sqlite3OsLock (lockType=1, id=) at sqlite3.c:21299 #5 pagerLockDb (pPager=pPager@entry=0x74a25008, eLock=eLock@entry=1) at sqlite3.c:50293 #6 0x77b1f34b in pagerLockDb (eLock=1, pPager=0x74a25008) at sqlite3.c:53054 #7 pager_wait_on_lock (pPager=pPager@entry=0x74a25008, locktype=locktype@entry=1) at sqlite3.c:53051 #8 0x77b55d6c in sqlite3PagerSharedLock (pPager=0x74a25008) at sqlite3.c:54293 #9 0x77b56835 in lockBtree (pBt=0x74a151e8) at sqlite3.c:64591 #10 sqlite3BtreeBeginTrans (p=0x74a1b508, wrflag=wrflag@entry=0) at sqlite3.c:64956 #11 0x77b82997 in sqlite3InitOne (db=0x74a12008, iDb=iDb@entry=0, pzErrMsg=pzErrMsg@entry=0x751fe778) at sqlite3.c:119558 #12 0x77b82aca in sqlite3Init (db=0x74a12008, pzErrMsg=pzErrMsg@entry=0x751fe778) at sqlite3.c:119740 #13 0x77b82b00 in sqlite3ReadSchema (pParse=pParse@entry=0x751fe770) at sqlite3.c:119765 #14 0x77b8d8e4 in sqlite3Pragma (pParse=0x751fe770, pId1=pId1@entry=0x751fddd0, pId2=pId2@entry=0x751fdde8, pValue=pValue@entry=0x751fde18, minusFlag=minusFlag@entry=0) at sqlite3.c:117300 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performing a SELECT on an enormous database...
Richard, On Saturday, October 25, 2014 05:31:35 AM Richard Hipp wrote: > However, when updating a row, SQLite rewrites the entire row. (It has to, > because of the use of variable-width encodings, since a change to any field > effects the location of all subsequent fields.) So if you have a row with > both a BOOLEAN and a 1MB BLOB, you have to write 1MB in order to change the > value of the BOOLEAN. Does this still apply if the column was added due to "alter table X add column"? I ask because it was my understanding that alter table added the extra column "elsewhere". It seems as a workaround, you could create a table with some metadata, then add each huge blob at the end of it with alter table. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieve a int or a sqlite_int64
On Tuesday, May 13, 2014 12:02:27 PM gwenn wrote: > Is there any way to differentiate one value persisted with > sqlite3_bind_int from another persisted with sqlite3_bind_int64 ? No, there's no difference > How to know which method between sqlite3_value_int and > sqlite3_value_int64 should be used to retrieve the value back ? Load it with sqlite3_value_int64 every time. If the number fits in a 32 bit integer, then you can store it in one. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] More LSM leak
On Thursday, May 08, 2014 03:16:57 AM Dan Kennedy wrote: > On 05/08/2014 06:59 AM, Charles Samuels wrote: > > This leak cursor leak can be consistently reproduced by my test program, > > but it doesn't occur every time you create and delete the cursor. > > Hi, > > Thanks for doing this. > > I don't think the script is closing all the connections. There are > 4 "open" commands and only 1 "close". I started adding a few "close" > commands, which seemed to help, but then I got an LSM_MISUSE error > (the tester program threw an exception) indicating that I was trying > to close a connection without closing all of its cursors first. You're right! I just noticed that I was doing something dumb while generating the trace that was causing certain closes to not be traced. > > Even so - the "8 bytes definitely lost" is suspicious. I think that > one at least might be a real leak. It's not, now that I've fixed my problem. > > I'm guessing the smaller.trace script is somehow created automatically. Yes, definitely! The larger.trace is 4.8 GiB. Thanks, Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] More LSM leak
This leak cursor leak can be consistently reproduced by my test program, but it doesn't occur every time you create and delete the cursor. The files you'll need are: http://www.derkarl.org/~charles/lsm/smaller.trace.bz2 http://www.derkarl.org/~charles/lsm/runlsm.cpp (The latter of which has changed since the last time I provided it to this list) $ cat smaller.trace | valgrind --leak-check=yes ~/a.out lsm ==24046== Memcheck, a memory error detector ==24046== Copyright (C) 2002-2011, and GNU GPL'd, by Julian Seward et al. ==24046== Using Valgrind-3.7.0 and LibVEX; rerun with -h for copyright info ==24046== Command: /home/charles/a.out lsm ==24046== ==24046== ==24046== HEAP SUMMARY: ==24046== in use at exit: 39,507 bytes in 24 blocks ==24046== total heap usage: 4,406,026 allocs, 4,406,002 frees, 149,648,738 bytes allocated ==24046== ==24046== 8 bytes in 1 blocks are definitely lost in loss record 1 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x40B000: lsmMallocZeroRc (lsm_mem.c:69) ==24046==by 0x40F5DC: multiCursorAddAll.isra.21 (lsm_sorted.c:2387) ==24046==by 0x40F64C: multiCursorInit (lsm_sorted.c:2400) ==24046==by 0x411C6E: lsmMCursorNew (lsm_sorted.c:2495) ==24046==by 0x40A52F: lsm_csr_open (lsm_main.c:774) ==24046==by 0x405539: main (runlsm.cpp:255) ==24046== ==24046== 9 bytes in 1 blocks are possibly lost in loss record 2 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40B038: lsmReallocOrFree (lsm_mem.c:79) ==24046==by 0x40E501: sortedBlobSet (lsm_sorted.c:373) ==24046==by 0x40EB90: multiCursorCacheKey (lsm_sorted.c:2690) ==24046==by 0x4130E4: lsmMCursorSeek (lsm_sorted.c:3077) ==24046==by 0x405414: main (runlsm.cpp:242) ==24046== ==24046== 24 bytes in 1 blocks are possibly lost in loss record 3 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x41A5AD: lsmPosixOsMutexNew (lsm_unix.c:654) ==24046==by 0x40D59D: lsmDbDatabaseConnect (lsm_shared.c:465) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046== ==24046== 24 bytes in 1 blocks are possibly lost in loss record 4 of 22 ==24046==at 0x4C28CCE: realloc (vg_replace_malloc.c:632) ==24046==by 0x41A64E: lsmPosixOsRealloc (lsm_unix.c:499) ==24046==by 0x41AEAE: lsmPosixOsShmMap (lsm_unix.c:400) ==24046==by 0x40BAF7: lsmShmCacheChunks (lsm_shared.c:1688) ==24046==by 0x416F5C: treeShmChunkRc (lsm_tree.c:318) ==24046==by 0x4185AD: lsmTreeInit (lsm_tree.c:1127) ==24046==by 0x449277: lsmLogRecover (lsm_log.c:972) ==24046==by 0x40D7AF: lsmDbDatabaseConnect (lsm_shared.c:365) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046== ==24046== 40 bytes in 1 blocks are possibly lost in loss record 5 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x40B000: lsmMallocZeroRc (lsm_mem.c:69) ==24046==by 0x40E14E: multiCursorAllocTree (lsm_sorted.c:2680) ==24046==by 0x41308C: lsmMCursorSeek (lsm_sorted.c:3066) ==24046==by 0x405414: main (runlsm.cpp:242) ==24046== ==24046== 48 bytes in 2 blocks are possibly lost in loss record 6 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40AF95: lsmMallocZero (lsm_mem.c:50) ==24046==by 0x40B000: lsmMallocZeroRc (lsm_mem.c:69) ==24046==by 0x445142: lsmFsOpen (lsm_file.c:660) ==24046==by 0x40D67F: lsmDbDatabaseConnect (lsm_shared.c:506) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046== ==24046== 56 bytes in 1 blocks are possibly lost in loss record 7 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x40B038: lsmReallocOrFree (lsm_mem.c:79) ==24046==by 0x40E501: sortedBlobSet (lsm_sorted.c:373) ==24046==by 0x413FB2: lsmMCursorValue (lsm_sorted.c:3309) ==24046==by 0x405008: main (runlsm.cpp:205) ==24046== ==24046== 64 bytes in 1 blocks are possibly lost in loss record 8 of 22 ==24046==at 0x4C28BED: malloc (vg_replace_malloc.c:263) ==24046==by 0x41A61B: lsmPosixOsMalloc (lsm_unix.c:472) ==24046==by 0x41A8E2: lsmPosixOsOpen (lsm_unix.c:81) ==24046==by 0x40D852: lsmDbDatabaseConnect (lsm_shared.c:412) ==24046==by 0x409465: lsm_open (lsm_main.c:188) ==24046==by 0x404B63: main (runlsm.cpp:146) ==24046==
[sqlite] sqlite_master and sessions
Hi, I'm using the Sessions extension, and I'm seeing a problem in which when calling sqlite3session_changeset(), it calls sessionSelectStmt with zTab="sqlite_master", which fails because that table has no primary key. I'm doing sqlite3session_attach(d->sqlSession, 0), so that should include "all tables", and even though sqlite_master is in the set of "all tables", however, I don't think automatic modifications should be recorded anyway with sessions. xPreUpdate in slqite3session.c is called as a consequence of me doing a "drop index". I think this is a bug. I can workaround it by disabling sessions for the drop? Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite sessions: handling rollbacks
I'm using sqlite's sessions module, and I'm noticing that it doesn't appear to handle rollbacks. Specifically, if I'm recording a database, and then I rollback the database, the session module appears to still record the changes made by that rollback. Why does this apparently significant flaw exists this module? Is there an easy to way solve it? Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The "sessions" branch
On Thursday, May 31, 2012 11:53:18 f.h. Richard Hipp wrote: > On Thu, May 31, 2012 at 2:40 PM, Charles Samuels wrote: > > So, I'd like to ask what's wrong with the session extension that it's > > never been rolled into the main distribution and hasn't been worked on > > since 2011 July? > > The sessions branch is stable and fully supported and will continue to be > supported. What's the right way to go about using it? I have a fossil tree and I can switch to the branch, but how do I make sure that it's merged with 3.7.12.1 and also becomes part of the amalgamation tree? Sorry about the potentially stupid questions. > But I got negative feedback from the community when I proposed > it, so we've kept it out of trunk. You'll get positive feedback from *this* member of the community :) Seems strange that it's not at least off by default with an optional #define, in trunk. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The "sessions" branch
Hi, I couldn't help but notice that in the sqlite fossil repository, there's a branch named "sessions" which has this: http://www.sqlite.org/cgi/src/dir?name=ext/session This is a very useful feature for me because it would allow me to rollback a change after it's been committed (if somehow I've discovered that it's wrong). So, I'd like to ask what's wrong with the session extension that it's never been rolled into the main distribution and hasn't been worked on since 2011 July? Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 2:02:39 e.h. Igor Tandetnik wrote: > How about this. You create two views, say V1 and V2, one with the join > and one without. Take user-provided query, replace all occurences of V1 > with V2, and try to prepare the new query. If that succeeds, the new > query is syntactically well-formed and you can execute it. Otherwise, > fall back to running the original query. That's a great idea! As there's a bunch of joins, I'd need to find the right permutation to reduce the number of joins. But that shouldn't take too long. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 1:51:54 e.h. Marc L. Allen wrote: > So, the user provides a standard SQL query using your composite view? Or > are they providing some other construct that you convert into a query > using your view? The user speaks SQL. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 1:10:17 e.h. Marc L. Allen wrote: > Wouldn't it be easier to construct a couple of different views and then > intelligently decide which view to use based on the user request? If I could determine which tables the user were interested in and then shift the view beneath the user without the user knowing, then that'd work. > You have a specific requirement and you're wanting SQLite to add overhead > to every single query it executes. All requirements are specific :) How do you pick at what point that overhead is too much? Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 12:03:02 e.h. Marc L. Allen wrote: > 1) The left outer join table is joined by a unique column AND > 2) No other data from the joined table is used in the query. > > Is that about right? Almost: add "recursively": I actually have it nested with *another join* with the same features, both of which can safely be discarded. And the "unique column" is actually a composite unique constraint. > > Out of curiosity, why is code being written like the SQL you're > providing? Is it automatically generated? It this case, it actually is automatically generated only to keep me from having to redundantly write the join over and over again. A. I have a table of Things. B. I have a table mapping those Things to object IDs in another datastore. C. I have a virtual table module exposing that other datastore. And I have a left outer join between A and B and then another between B and C. And then I put all of those in one giant (almost: only about 15 columns) view that the user can do arbitrary queries on, most of which only touch one or two of those columns from C, and many of which touch zero. There are multiple Bs per A, but one C per B. > > How often does this sort of query come up? Right now, I want to make this the primary interface to my application, but the queries mostly come from users, so one every few seconds. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 11:29:00 f.h. Black, Michael (IS) wrote: > Since you have a one-to-one relationship I'm not sure why you don't just > put the inseam with the Employee, but perhaps you're just giving an > example here. It is an example. > > I would do it this way which is going to run a heck of lot faster than > using string compares as you are doing. Perhaps it runs faster, but it still does the unnecessary search into right- hand table: sqlite> explain query plan select name from employee inner join Uniform on employee.id=uniform.employeeid; 0|0|0|SCAN TABLE employee (~100 rows) 0|1|1|SEARCH TABLE Uniform USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) (this actual query goes into a view that I make available to app's "customers") > Or do this which I think does exactliy what you are thinking... > sqlite> select name from Employee where id in (select employeeid from > Uniform); Nope, that's effectively a inner join instead of an outer join. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 10:14:22 f.h. Charles Samuels wrote: > sqlite> select Name from Employee join Uniform on > Employee.name=Uniform.employeename; > Joe > Dave > sqlite> explain query plan select Name from Employee join Uniform on > Employee.name=Uniform.employeename; > 0|0|0|SCAN TABLE Employee (~100 rows) > 0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1 > (employeename=?) (~1 rows) I mean "left outer join" here! sqlite> select Name from Employee left outer join Uniform on Employee.name=Uniform.employeename; Joe Steve Eric Dave sqlite> explain query plan select Name from Employee left outer join Uniform on Employee.name=Uniform.employeename; 0|0|0|SCAN TABLE Employee (~100 rows) 0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1 (employeename=?) (~1 rows) Charles (can never proofread enough.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 10:02:24 f.h. Igor Tandetnik wrote: > On 5/29/2012 8:21 PM, Charles Samuels wrote: > > Suppose you have a query like this: > > > > select Employee.name from Employees left join Uniform on > > > > (EmployeeSize.name=Uniform.employeeName) > > Doesn't look like a valid query to me. What's Employee and EmployeeSize? > I assume you meant Employees in both places. EmployeeSize should mean "Employee". I seem to never be able to proofread an email enough. > > > This query's result should be identical weather or not we have that join; > > it's an outer join, not an inner join, afterall. However, explain query > > plan (and my time measurements) seem to indicate that the the query with > > the join is far slower/more complex. > > > > Is it hypothetically possible that the optimizer could avoid the join? > > In principle, yes. > > > Is there a way to get sqlite to do so? > > Sure. Remove the join from the query. That's not a helpful answer because I can't know to do so without parsing the SQL myself. > > > Is this a planned feature? > > Well, it's not a feature, it's a lack thereof. The query planner is not > specifically looking for this particular optimization opportunity - > probably because such situations are uncommon and are not worth the time > looking for. Your question seems to suggest that optimizations magically > happen by themselves unless someone specifically writes code to block > them; in reality, it's the other way round. "A planned feature" refers to a feature that does not yet exist. I am asking if sqlite will ever be able to do this optimization? cs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Wednesday, May 30, 2012 9:45:16 f.h. David Bicking wrote:
> If Uniform has a given EmployeeName twice, you will get the Employee.Name
> twice in this query. Thus it would be a different result than if you did
> not join with Uniform.
On Wednesday, May 30, 2012 9:57:00 f.h. Petite Abeille wrote:
> The outer join could affect the cardinality of the result (i.e. there could
> be multiple rows matching the driving table). Therefore, no, it cannot be
> discarded without additional information regarding the nature of the
> reference (i.e. to-one).
Uniform has a unique constraint on EmployeeName, is this not the necessary
"additional information"? Is it possible for me to give the query optimizer
this necessary information so it could do the optimization?
sqlite> create table Employee (name);
sqlite> create table Uniform (employeename, inseam, constraint ue unique
(employeename));
sqlite> insert into employee values ("Joe");
sqlite> insert into employee values ("Steve");
sqlite> insert into employee values ("Eric");
sqlite> insert into employee values ("Dave");
sqlite> insert into Uniform values ("Joe", 77);
sqlite> insert into Uniform values ("Dave", 81);
sqlite> select Name from Employee join Uniform on
Employee.name=Uniform.employeename;
Joe
Dave
sqlite> explain query plan select Name from Employee join Uniform on
Employee.name=Uniform.employeename;
0|0|0|SCAN TABLE Employee (~100 rows)
0|1|1|SEARCH TABLE Uniform USING COVERING INDEX sqlite_autoindex_Uniform_1
(employeename=?) (~1 rows)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why can't sqlite disregard unused outer joins?
Suppose you have a query like this: select Employee.name from Employees left join Uniform on (EmployeeSize.name=Uniform.employeeName) This query's result should be identical weather or not we have that join; it's an outer join, not an inner join, afterall. However, explain query plan (and my time measurements) seem to indicate that the the query with the join is far slower/more complex. Is it hypothetically possible that the optimizer could avoid the join? Is there a way to get sqlite to do so? Is this a planned feature? Why do I ask? I have a big view that joins a whole bunch of a tables (including with a virtual table), but most queries use only a subset of those queries actual data and I'd rather not get hit by that performance penalty. Since my queries come from the user, I don't want to have them do the joins on their own, it'd be tedious. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On Wednesday, May 16, 2012 1:28:17 e.h. Simon Slavin wrote: > On Tue, May 15, 2012 at 1:55 PM, Charles Samuels wrote: > > At some point, we get a checkpoint; at this instant, what is in otherdb > > and what is in sqlite is what we want committed to sqlite, if either of > > them fails, we can rollback both of them and both databases return to a > > consistent > > state of a previous checkpoint. The problem is that in the time between > > checkpoint 1 and checkpoint 1 being committed to disk, more data is > > arriving. > > You will find that in almost all situations where either of them failed, > whatever method you're using to protect the integrity of your data will > fail too, because it will depend on the same thing. Not only that, but > that your method of assuring synchrony is likely to add lots of > complication which will make the whole system fail more than a simple > one-SQL-engine implementation would. For data integrity of the type you > describe, there's nothing much you can do short of using duplicate servers > talking to RAIDs with redundancy. Sorry, I misspoke here a little bit and I think it's confusing you to the actual problem I'm having. To clarify: After I do a commit to "otherdb" - I'm *still* able to atomically roll it back, simply because I keep history of its previous versions in the sqlite db. I have it such that if the sqlite database itself rolls back, then the data in otherdb automatically does as well (it involves lots of COW pages and such). This is a solved problem. The real problem is that while I'm waiting for "otherdb" to synchronize, more data is still coming in to this entire solution. Otherdb is able to take that data and put it in a "future version", but sqlite can't. After otherdb synchronizes (i.e., with fsync), I can commit the sqlite db and even if otherdb has more stuff of a "future version" added to it, the sqlite database causes that "future data" to be safely ignored. However, while I can safely add to otherdb's "future data", there's no place to add it into SQLite, as I still haven't committed sqlite's "present version" - I can't do that until otherdb reports that it's committed its entire present version. > [...] > > To reduce the complication you have already noted, I recommend you try to > divorce the versioning system from the code of your project. Try to write > yourself a database library which would be useful for many projects, > rather than have any one routine which includes both knowledge about your > project and knowledge about how versioning works. I have already done that; I have SQL-friendly data stored in SQLite, and non- SQL data stored in otherdb, but either I can't accept new data while waiting for otherdb to do its full fsync (because I have nowhere to put the sqlite- side of things), or I need a way to have sqlite not commit the stuff from between the start of otherdb's checkpoint and the point that it finishes its fsync. > > Another way to do it is simply to use ROLLBACK points as defined in the SQL > standard and implemented by SQLite. We already know that they work > correctly and there is a lot of documentation on their use and drawbacks. If by rollback points, you mean savepoints; then they're simply not useful if they discard (as opposed to giving me back the data) the data that is being rolled-back. To keep that data, I would need a shadow-database of some sort, which would of course require me to have many many routines that include "knowledge of my project and knowledge of how versioning works". Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On Wednesday, May 16, 2012 11:33:02 f.h. Igor Tandetnik wrote: > Well, SQLite doesn't. Basically, you want SQLite to maintain multiple > not-yet-committed versions of the same record. Well, yes, but it already does; you can rollback a savepoint. If I could rollback a savepoint and then unrollback it after doing an commit, that'd be the equivalent, if I could trick the paging module. > I don't believe there's > any way to get it to do that automatically - you'll have to come up with > an explicit versioning scheme (like your idea of creating a temporary > shadow database). I'm trying that now, it involves temporary tables and views; but the bookkeeping for it is looking to be increasingly difficult and it is also going to make my own code unmaintainable. I'm willing to pay the sqlite team to add this feature to a future of sqlite. Charles p.s. please excuse my Subject line. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please review this email to sqlite's mailing list
On Wednesday, May 16, 2012 10:00:37 f.h. Adam DeVita wrote: > Did you check out > http://www.sqlite.org/inmemorydb.html > > Could you use an in-memory db to act as a db for a save point? Yes, but this is incredibly hard: I have to maintain the schema twice, I can't allow the user to enter their own SQL statements, I still have to copy all the accumulated stuff over, and schema changes would be very difficult to handle. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please review this email to sqlite's mailing list
I'm using sqlite in addition to another database ("otherdb") storing data in a
specific manner. I'm trying to keep atomicity of my disk commits. It can take
several minutes for otherdb to commit, and while it commits it can already
start accumulating data for a future transaction.
Some of the data coming into this application also goes into the sqlite
database. But I'd like to keep what's "on the oxide" between sqlite and
otherdb consistent with eachother. Let's accept that otherdb
At some point, we get a checkpoint; at this instant, what is in otherdb and
what is in sqlite is what we want committed to sqlite, if either of them
fails, we can rollback both of them and both databases return to a consistent
state of a previous checkpoint. The problem is that in the time between
checkpoint 1 and checkpoint 1 being committed to disk, more data is arriving.
The question here is: where can I put that "more data" so that it won't be
part of checkpoint 1, but is still accessable by sqlite select statements?
(Accept that otherdb allows asychronous commits such that I can add more data
to it that doesn't wind up on disk).
There's a few possibilities with some serious disadvantages:
* When otherdb completes its checkpoint, I commit sqlite; until otherdb and
sqlite finish their commits, any data going into sqlite instead goes into a
"mirror" sqlite that I can do queries against meanwhile (but then I have to
replay *all* of those modifications against the primary sqlite). This can cost
huge amounts of memory because the sqlite database can get big: 3GiB or more.
It's also slow because all of a sudden I have to do a whole bunch of sqlite
statements. It's even slower because now any update I do *normally* has to be
cloned.
* I could write a virtual filesystem layer for sqlite that somehow accumulates
changes that I can merge in with insert statements. So it's like the previous
solution but I use some arm waving in combination with smoke and mirrors to at
least not make me have two total copies of the database. The problem with this
one is I don't know how to do it, and even if I did, I wouldn't know how
reliable it was.
* If sqlite had a "commit transaction to savepoint X", then sqlite commits to
the oxide everything up to a specific savepoint, keeping the savepoints after
those committed still as active and uncommitted savepoints. The only
disadvantage I can think of to this is that sqlite has no such feature.
So how could I do this?
Charles
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?
On Tuesday, June 28, 2011 9:36:22 f.h. Stephan Beal wrote: > There is NOTHING wrong with mixing .c and .cpp files in one C++ project. > Compile the C code with gcc and C++ code with g++, and then link them > together as you would any other objects. Compiling sqlite as C++ is "hopeless", so this is a good idea! However, be warned that if you use exceptions, you can't use sqlite3_exec, because then the exceptions can't make it through the C code. It's easy enough to roll your own sqlite3_exec and compile it as C++. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to get a reliable Integer from a Real?
On Wednesday, February 02, 2011 11:06:59 am Drake Wilson wrote: > Quoth Oliver Peters , on 2011-02-02 18:25:04 +: > > I'm on Win XP with sqlite 3.7.5 and trying to get cents from euro but > > sometimes > > Don't do that. ... Most people doing > currency calculations should be using integers and treating them as > fixed-point values. Oliver, This is very good advice. :) Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Committing to a savepointL
On Thursday, January 13, 2011 7:55:28 pm Pavel Ivanov wrote: > What you really want is for database engine to allow to have two > parallel writing transactions and for it to not lock the whole > database in those transactions but do some fine-grained locking > instead. Well, that would work, but what I need is far simpler. Sqlite today has savepoints which I interpret as recursive transactions. As a consequence of savepoints, sqlite already knows the boundary between multiple savepoints. I suppose they are implemented as copy-on-writing pages that have been copy-on-writed themselves. Example: insert 1 savepoint A insert 2 rollback to A so "savepoint A" means that further changes should go into a different set of copy-on-write pages. "rollback to A" then undoes "insert 2" by discarding those COW pages created by "insert 2" but keeping the COW pages created by "insert 1". So, similarly, one could implement "commit to savepoint A" by taking the COW pages created by "insert 1" and committing them to disk, but keeping the COW pages created by "insert 2" uncommitted. I suppose hypothetically, I could get what I want (with a huge performance penalty) by just keeping a list of the actual SQLite statements I made represented by "insert 2" above, doing a rollback to A and a commit, then replaying those "insert 2" statements. That way I can continue to make sqlite queries on data that isn't going to be committed at the next checkpoint. However, this would perform a lot more poorly then doing it at a page level. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Committing to a savepoint
On Thursday, January 13, 2011 3:23:55 pm Simon Slavin wrote: > Look at savepoints: > > http://www.sqlite.org/lang_savepoint.html Yes, I have been, and I use them quite a bit. However, they don't appear to meet my needs, which is why I asked my question. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Committing to a savepoint
Hi, I have a program that uses sqlite to do bookkeeping for another set of data. I do a commit on sqlite once I'm certain the other set of data is done. While I'm waiting for the other set of data to process, I might make other changes to my Sqlite database. However, I don't want to commit the stuff made after I started to process the other set of data. In short, I want a "commit to savepoint savepoint-name" which commits up until a savepoint begins, but not anything after. Here's more or less what I need: A * sqlite gets some inserts B * we're at a checkpoint, so everything after this point shouldn't get committed now. So "savepoint SP" C * insert some more into sqlite D * The checkpoint is ready to go, so we do "commit to savepoint SP" E * now, on-disk, the sqlite db contains everything in step A, but nothing in step C In this example, doing the commit at D is the same as doing in between A and B. Is there a way to do this today? How hard would it be to add "commit to savepoint" if not? Thanks, Charles ps: I think the documentation on savepoint is a little bit unclear on if "rollback to savepoint" and "release savepoint" keep the savepoint in question as the active savepoint. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Massive performance regression in 3.7.x
Greetings, With the sqlite database here: http://www.derkarl.org/~charles/massive_performance_regression.bz2 There is a massive performance regression between 3.7.1 and 3.7.2, when compared to 3.6.23.1. The following query runs orders of magnitude slower than it did in the previous version: select * from object_formats join page_table on page_table.version=3 and page_table.objectid = object_formats.objectid ; Thanks, Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] transactions in one thread
I need to be able to see the committed version of a database while a new transaction is in the works. I would like to open the same database file twice in a single thread, start a transaction on one of the database connections, make a few writes to that database, then on the other database connection do some reads on the other connection. Is this safe, and will it work? Thanks, Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
