Re: [sqlite] sqlite performance, locking & threading
On Thu, Jan 04, 2007 at 12:50:01AM +, Emerson Clarke wrote: > My oppologies, your right that explanation had been given. OK. > But i didnt actually take it seriously, i guess i found it hard to > believe that it being the easier option was the only reason why this > limitation was in place. SQLite is a large pile of code. Other libraries that I'm familiar with that have taken this approach are larger still. Retrofitting MT-safety into these is hard, so the easiest path is often taken. (It may be that SQLite was always intended to be MT-safe, but I don't know that for a fact.) > If this is the case, then surely the fix is simple. Given that i > assume it is safe to have multiple sqlite3_step() calls active on a > single connection on a single thread. And given what you have said > about sqlite not already checking data structures that would be shared > by multiple threads, then surely all that needs to happen is for the > misuse detection to be removed. Your first assumption, as has been explained repeatedly, is incorrect. Oh, wait. I think I understand what's happening. You've missunderstood what you've been told (your next paragraph makes me think so). You *can* use sqlite3_step() with the same db context in multiple threads, you just have to synchronize so this doesn't happen *concurrently*. If you remove the misuse detection but don't synchronize I believe you'll find that your application will crash or worse. > Since there is usually nothing which needs to be done to specifically > make any api thread safe other than synchronising access too it. If > by synchronising access to the api calls i can ensure that no two > threads use any data structure at the same time, everything should > work fine right ? Yes. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite performance, locking & threading
Nicholas, My oppologies, your right that explanation had been given. But i didnt actually take it seriously, i guess i found it hard to believe that it being the easier option was the only reason why this limitation was in place. If this is the case, then surely the fix is simple. Given that i assume it is safe to have multiple sqlite3_step() calls active on a single connection on a single thread. And given what you have said about sqlite not already checking data structures that would be shared by multiple threads, then surely all that needs to happen is for the misuse detection to be removed. Since there is usually nothing which needs to be done to specifically make any api thread safe other than synchronising access too it. If by synchronising access to the api calls i can ensure that no two threads use any data structure at the same time, everything should work fine right ? This gets to the very core of the original issue i had, and why i said sqlite was "actively" thread unsafe. Because it terminates itself with misuse errors even when in theory it is perfectly safe to be used with multiple threads provided the user synchronises on every api call, which is easily achieved. Am i making any sense ? Emerson On 1/3/07, Nicolas Williams <[EMAIL PROTECTED]> wrote: On Wed, Jan 03, 2007 at 11:22:36PM +, Emerson Clarke wrote: > Ok, > > Well can you do me a favour and forward me the email where this was > supposedly explained in all its technical glory. Technical glory? No. At a high level it's this: that it is easier to make an API like SQLite's thread-safe with exceptions like "only one thread active in any given context object at any time" than it is to put mutexes and what not around data structures that would be shared by multiple threads if this exception were not stated. Unless and until you try to do it the other way you'll find this rationale to be subjective. Like I said, good luck. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] calculate age
> LOL! You should look at a function to determine if a day is a holiday. > Talk about ugly! In some places you literally need to know the weather > and the phase of the moon! > > -- [EMAIL PROTECTED] ~/movie]$ pom The Moon is Waning Gibbous (100% of Full) [EMAIL PROTECTED] ~/movie]$ which pom /usr/games/pom is there a shell script program to let me know if the weather is clear in mecca? ;) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] RE: cant sum rows
yes, thank you very much for your help. its work now :) Downey, Shawn wrote: > > I think you created a column in table1 named "int" of type "id". > > Try: > > CREATE TABLE table2 (id int); > > Then sum() should work. > > Shawn M. Downey > MPR Associates > 10 Maxwell Drive, Suite 204 > Clifton Park, New York 12065 > 518-371-3983 x113 (work) > 860-508-5015 (cell) > > -Original Message- > From: LoGi [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 03, 2007 2:13 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] cant sum rows > > > I create table with this sql: > CREATE TABLE table1 (int id); > > and fill with some randomize values > > I try this sql: > SELECT SUM(id) FROM table1 > > to sum the table rows but its return error no such column: id > > > i use the sqlite from a c++ code > with this function > sqlite3_get_table(); > > > > thanks. > > -- > View this message in context: > http://www.nabble.com/cant-sum-rows-tf2915362.html#a8146664 > Sent from the SQLite mailing list archive at Nabble.com. > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/cant-sum-rows-tf2915362.html#a8147569 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] sqlite performance, locking & threading
Hi Emerson, I just hope you don't reinvent the wheel ;) I haven't yet had the need to index things the way you describe it. May be I should take that as one of my next pet projects to get a handle on this type of task. The problem as I see it is basically, that any way you design this: If the storage tasks take 90% of your indexing time, then any parallelization may be a waste of effort. Even if you use a synchronization object you're essentially serializing things in a (complicated) multithreaded way... As far as static initialization: That it occurs before main() and is out of your control was the point I was getting across. That's why I wrote that this type of initialization should be avoided, unless there's no better design for it. Michael -Ursprüngliche Nachricht- Von: Emerson Clarke [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. Januar 2007 20:31 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite performance, locking & threading Michael, Thanks for the advice. During the indexing process i need to select and optionally insert records into a table so i cant ignore the outcomes. Basically the indexing process does compression, so for each document it inserts words into a table and looks up keys. Every word in the document gets swapped with a key, and new keys are inserted as needed. There are some problems with splitting the work up in a different way as you suggested. I would either end up with a lot of queues or i would have to stagger the work so that the entire data set gets processed in stages which doesnt scale very well and isnt particularly fault tollerant. When building an index, you want the structure to be built up progressively, so that you can pause the process and resume it later on whilst still having useful results. I would be worried that in a queued design, the overhead and bottlenecks caused by the buffering, message passing, and context switching would reduce the performance to that of a single thread. Especially since the database operations represent 90% of the work, all you would really be doing is attempting to serialise things in a multithreaded way. Im sure having worked on multithreaded systems you appreciate that sometimes simple designs are better, and i think i have a pretty good handle on what it is that im trying to do. You never have control over static initialisation, it happens before main(). If i was writing very specific code to suit just this situation then maybe as you say i wouldnt need to worry about it. But im also writing a database api, and that api is used for many different things. My considderations are not just for this one problem, but also for the best general way to code the api so that it is safe and efficient in all circumstances. So far the client/server design is the only way i can achieve true thread safety. If i could work out why sqlite3_step() causes problems across multiple threads i could probably make things a little faster and i could do away with the need for a client/server design. Emerson On 1/3/07, Michael Ruck <[EMAIL PROTECTED]> wrote: > Emerson, > > Now I understand your current implementation. You seemingly only > partially split up the work in your code. I'd schedule the database > operation and not wait on the outcome, but start on the next task. > When the database finishes and has retrieved its result, schedule some > work package on a third thread, which only processes the results etc. > Split up the work in to repetitive, non blocking tasks. Use multiple > queues and dedicated threads for parts of the operation or thread pools, which process queues in parallel if possible. > From what I can tell you're already half way there. > > I still don't see your static initialization problem, but that's > another story. Actually I'd avoid using static initialization or > static (singleton) instances, unless the design really requires it. > Someone must control startup of the entire process, have that one > (probably main/WinMain) take care that the work queues are available. > Afterwards the order of thread starts doesn't matter... Actually it is > non-deterministic anyway (unless you serialize this yourself.) > > Michael > > -Ursprüngliche Nachricht- > Von: Emerson Clarke [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 3. Januar 2007 15:14 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] sqlite performance, locking & threading > > Michael, > > Im not sure that atomic operations would be a suitable alternative. > The reason why im using events/conditions is so that the client thread > blocks until the server thread has processed the query and returned > the result. If i did not need the result then a simple queueing > system with atomic operations or critical sections would be fine i guess. > > The client thread must always block or spin until the server thread > has completed the query. Critical sections cant be efficiently used > to notify other threads of status
Re: [sqlite] sqlite performance, locking & threading
Michael, Thanks for the advice. During the indexing process i need to select and optionally insert records into a table so i cant ignore the outcomes. Basically the indexing process does compression, so for each document it inserts words into a table and looks up keys. Every word in the document gets swapped with a key, and new keys are inserted as needed. There are some problems with splitting the work up in a different way as you suggested. I would either end up with a lot of queues or i would have to stagger the work so that the entire data set gets processed in stages which doesnt scale very well and isnt particularly fault tollerant. When building an index, you want the structure to be built up progressively, so that you can pause the process and resume it later on whilst still having useful results. I would be worried that in a queued design, the overhead and bottlenecks caused by the buffering, message passing, and context switching would reduce the performance to that of a single thread. Especially since the database operations represent 90% of the work, all you would really be doing is attempting to serialise things in a multithreaded way. Im sure having worked on multithreaded systems you appreciate that sometimes simple designs are better, and i think i have a pretty good handle on what it is that im trying to do. You never have control over static initialisation, it happens before main(). If i was writing very specific code to suit just this situation then maybe as you say i wouldnt need to worry about it. But im also writing a database api, and that api is used for many different things. My considderations are not just for this one problem, but also for the best general way to code the api so that it is safe and efficient in all circumstances. So far the client/server design is the only way i can achieve true thread safety. If i could work out why sqlite3_step() causes problems across multiple threads i could probably make things a little faster and i could do away with the need for a client/server design. Emerson On 1/3/07, Michael Ruck <[EMAIL PROTECTED]> wrote: Emerson, Now I understand your current implementation. You seemingly only partially split up the work in your code. I'd schedule the database operation and not wait on the outcome, but start on the next task. When the database finishes and has retrieved its result, schedule some work package on a third thread, which only processes the results etc. Split up the work in to repetitive, non blocking tasks. Use multiple queues and dedicated threads for parts of the operation or thread pools, which process queues in parallel if possible. From what I can tell you're already half way there. I still don't see your static initialization problem, but that's another story. Actually I'd avoid using static initialization or static (singleton) instances, unless the design really requires it. Someone must control startup of the entire process, have that one (probably main/WinMain) take care that the work queues are available. Afterwards the order of thread starts doesn't matter... Actually it is non-deterministic anyway (unless you serialize this yourself.) Michael -Ursprüngliche Nachricht- Von: Emerson Clarke [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. Januar 2007 15:14 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite performance, locking & threading Michael, Im not sure that atomic operations would be a suitable alternative. The reason why im using events/conditions is so that the client thread blocks until the server thread has processed the query and returned the result. If i did not need the result then a simple queueing system with atomic operations or critical sections would be fine i guess. The client thread must always block or spin until the server thread has completed the query. Critical sections cant be efficiently used to notify other threads of status change. I did try using critical sections in this way, by spinning until the server thread takes a lock, then blocking and eventually waiting for the server thread to finish. But since there is no way to block the server thread when there is no work to do both the client and server thread must sleep which induces context switching anyway. If you used atomic operations, how would you get the client thread to block and the server thread to block when it is not processing ? Events/conditions seemed to be the best solution, the server thread never runs when it doesnt need to and always wakes up when there is processing to be done. The static initialisation problem occurs becuase the server thread must be running before anything which needs to use it. If you have a static instance of a class which accesses a database and it is initalised before the static instance which controls the server thread, you have a problem. It can be overcome using the initialise on first use idiom, as long as your careful to protect the initalisation with
RE: [sqlite] cant sum rows
I think you created a column in table1 named "int" of type "id". Try: CREATE TABLE table2 (id int); Then sum() should work. Shawn M. Downey MPR Associates 10 Maxwell Drive, Suite 204 Clifton Park, New York 12065 518-371-3983 x113 (work) 860-508-5015 (cell) -Original Message- From: LoGi [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 03, 2007 2:13 PM To: sqlite-users@sqlite.org Subject: [sqlite] cant sum rows I create table with this sql: CREATE TABLE table1 (int id); and fill with some randomize values I try this sql: SELECT SUM(id) FROM table1 to sum the table rows but its return error no such column: id i use the sqlite from a c++ code with this function sqlite3_get_table(); thanks. -- View this message in context: http://www.nabble.com/cant-sum-rows-tf2915362.html#a8146664 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] cant sum rows
I create table with this sql: CREATE TABLE table1 (int id); and fill with some randomize values I try this sql: SELECT SUM(id) FROM table1 to sum the table rows but its return error no such column: id i use the sqlite from a c++ code with this function sqlite3_get_table(); thanks. -- View this message in context: http://www.nabble.com/cant-sum-rows-tf2915362.html#a8146664 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] minor doc issue; all docs referenced/build _except_ "whentouse.html" ...
after building docs, on the "docs.html" page, there's a reference to "whentouse.html", AppropriateUsesForSQLite when i click on it, it's a missing page. looking in mybuild dir, % ls doc/ arch.html datatype3.html oldnews.html arch2.gif datatypes.html omitted.html autoinc.htmldocs.htmlopcode.html c_interface.htmldownload.htmlpragma.html capi3.html faq.html quickstart.html capi3ref.html fileformat.html speed.html changes.htmlformatchng.html sqlite.gif compile.htmlindex.html sqlite.html conflict.html lang.htmlsupport.html copyright-release.html lockingv3.html tclsqlite.html copyright-release.pdf mingw.html vdbe.html copyright.html nulls.html version3.html it is, apparently, missing. looking in src tree, it *is* there. and referenced in main.mk. looking in my build's Makefile, i find: (...) # Rules used to build documentation # arch.html: $(TOP)/www/arch.tcl tclsh $(TOP)/www/arch.tcl >arch.html (...) vdbe.html: $(TOP)/www/vdbe.tcl tclsh $(TOP)/www/vdbe.tcl >vdbe.html version3.html: $(TOP)/www/version3.tcl tclsh $(TOP)/www/version3.tcl >version3.html --> # Files to be published on the website. # DOC = \ arch.html \ (...) vdbe.html \ version3.html --> doc:common.tcl $(DOC) mkdir -p doc mv $(DOC) doc (...) where there are references to 'whentouse' missing ( --> ) from main.mk. staring at main.mk, i'm not seeing why whentouse is NOT making it into _my_Makefile, but every other doc reference is. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite performance, locking & threading
On Tue, Jan 02, 2007 at 11:56:42PM +, Emerson Clarke wrote: > The single connection multiple thread alternative apparently has > problems with sqlite3_step being active on more than one thread at the > same moment, so cannot easily be used in a safe way. But it is by far > the fastest and simplest alternative. No, not "apparently" -- it _does_. What you should do is keep a set of db contexts and assign them to clients/connections and make sure that each clients/connections is only every serviced by one thread at a time. One way to do this is to classify incoming messages, select an existing object representing that client/connection/whatever or create a new one, then queue the new message in that object and queue this object up for dispatch to a worker thread. Cheers, Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] 2.8.6 Documentation
Tim Keeler wrote: Is there a url or somewhere I can get the sqlite 2.8.6 syntax documentation? Tim, The documentation on the website is released along with the source for each version of sqlite. For version 2.8.6 you would need to download that source archive and build the documentation from the source files. Usually you can just replace the version number in the current link (i.e. http://www.sqlite.org/sqlite-source-3_3_8.zip becomes http://www.sqlite.org/sqlite-source-2_8_16.zip) to get the download file you want, but version 2.8.6 seems to be too early for this (2.8.16 works though). The documentation source files contain TCL scripts that generate the HTML documentation pages. The last version of the SQL language documentation before version 3.0.0 can be found at http://www.sqlite.org/cvstrac/rlog?f=sqlite/www/lang.tcl by clicking the version 1.67 link. You can read most of the documentation from this script file directly, or if you have TCL installed you can probably expand this file into the desired HTML. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] odd indexing behavior
Thanks for the response, Joe. Unfortunately, since I have no way of knowing a priori what the relative number of returned rows is (without doing another query), using the plus is not really a work-around, it is a "don't use indexes" rule. I can't just arbitrarily assign the "plus" mark. Also, no other index was used, despite the plus on the time1 column. And 'order by' did not use the index. Has anyone else seen these problems? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: [RESOLVED/PATCH] re: "Error: no such function: randstr" @ v3.3.8 on OSX
[EMAIL PROTECTED] wrote: yeah, yeah. ... Call me cranky if you want. It probably just makes some gee-whiz syntax checker tool stop complaining. Whatever... Richard, You do sound a little cranky. :-) Perhaps its just post holiday blues. Remember its supposed to be a *Happy* New Year. ;-) Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Corruption - Probably Related to auto-vacuum
[EMAIL PROTECTED] wrote: > Check-in [3548] fixes a problem in the pager which can lead to > database corruption on a heavily loaded system running autovacuum. > I am continuing to analyze the problem in order to fully > characterize the circumstances under which database corruption > might occur. Once this analysis is complete, you can expect > to see the release version 3.3.9 containing the fix. > I am still attempting to characterize the circumstances under which database corruption can occur. I need additional data from Ron Aviel in order to continue with this analysis and he will likely be unavailable until tomorrow. So 3.3.9 will probably not be out until later this week. So far, the only path I have found that can lead to corruption is if two processes both try to rollback a hot journal at the same time. These two processes will race to get a lock on the database. Only one will succeed. The second process will back off. But that second process might have left its cache in an inconsistent state which could later result in database corruption. A hot journal can only result if a process that is in the middle of a write transaction dies or otherwise terminates without shutting down SQLite cleanly. Recap: The only path to corrupting a database so far discovered in the bug fixed by [3548] is as follows: (1) One process starts a write transaction, makes changes to the database which are incomplete, then aborts or exits without closing the database and completing the transaction. (2) Two other processes attempt to access the database at almost the same moment in time. Both see that the database was only partially updated in the previous step and both attempt to playback the journal in order to rollback the transaction. Only one will be successful at this. The other will back off. (3) The second of the two processes above, the one that did not playback the journal, goes on to make other changes to the database file based on an incorrect cache image - resulting in database corruption. This is a very unlikely sequence of events. Step (1) should not often happen on an otherwise well-behaved system. You will be very hard-pressed to make (2) happen unless you have multiple processors and even then the race condition appears to be very tight. There may be other paths which can exercise the problem, but this is the only one that I have found so far. Because this is so obscure, I think I am justified in waiting another day or two before push out version 3.3.9 in order to better understand what is going on. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] sqlite performance, locking & threading
Emerson, Now I understand your current implementation. You seemingly only partially split up the work in your code. I'd schedule the database operation and not wait on the outcome, but start on the next task. When the database finishes and has retrieved its result, schedule some work package on a third thread, which only processes the results etc. Split up the work in to repetitive, non blocking tasks. Use multiple queues and dedicated threads for parts of the operation or thread pools, which process queues in parallel if possible. >From what I can tell you're already half way there. I still don't see your static initialization problem, but that's another story. Actually I'd avoid using static initialization or static (singleton) instances, unless the design really requires it. Someone must control startup of the entire process, have that one (probably main/WinMain) take care that the work queues are available. Afterwards the order of thread starts doesn't matter... Actually it is non-deterministic anyway (unless you serialize this yourself.) Michael -Ursprüngliche Nachricht- Von: Emerson Clarke [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. Januar 2007 15:14 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite performance, locking & threading Michael, Im not sure that atomic operations would be a suitable alternative. The reason why im using events/conditions is so that the client thread blocks until the server thread has processed the query and returned the result. If i did not need the result then a simple queueing system with atomic operations or critical sections would be fine i guess. The client thread must always block or spin until the server thread has completed the query. Critical sections cant be efficiently used to notify other threads of status change. I did try using critical sections in this way, by spinning until the server thread takes a lock, then blocking and eventually waiting for the server thread to finish. But since there is no way to block the server thread when there is no work to do both the client and server thread must sleep which induces context switching anyway. If you used atomic operations, how would you get the client thread to block and the server thread to block when it is not processing ? Events/conditions seemed to be the best solution, the server thread never runs when it doesnt need to and always wakes up when there is processing to be done. The static initialisation problem occurs becuase the server thread must be running before anything which needs to use it. If you have a static instance of a class which accesses a database and it is initalised before the static instance which controls the server thread, you have a problem. It can be overcome using the initialise on first use idiom, as long as your careful to protect the initalisation with atomic operations, but its still a bit complicated. Emerson On 1/3/07, Michael Ruck <[EMAIL PROTECTED]> wrote: > Hi Emerson, > > Another remark: On Windows using Events synchronization objects > involves additional kernel context switches and thus slows you down > more than necessary. I'd suggest using a queue, which makes use of the > InterlockedXXX operations (I've implemented a number of those, > including priority based ones - so this is possible without taking a > single lock.) or to use critical sections - those only take the kernel > context switch if there really is lock contention. If you can reduce > the kernel context switches, you're performance will likely increase drastically. > > I also don't see the static initialization problem: The queue has to > be available before any thread is started. No thread has ownership of > the queue, except may be the main thread. > > Michael > > > -Ursprüngliche Nachricht- > Von: Emerson Clarke [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 3. Januar 2007 00:57 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] sqlite performance, locking & threading > > Nico, > > I have implemented all three strategies (thead specific connections, > single connection multiple threads, and single thread server with > multiple client threads). > > The problem with using thread specific contexts is that you cant have > a single global transaction which wraps all of those contexts. So you > end up having to use fine grained transactions, which decreases performance. > > The single connection multiple thread alternative apparently has > problems with sqlite3_step being active on more than one thread at the > same moment, so cannot easily be used in a safe way. But it is by far > the fastest and simplest alternative. > > The single thread server solution involves message passing between > threads, and even when this is done optimally with condition variables > (or events on > windows) and blocking ive found that it results in a high number of > context switches and decreased performance. It does however make a > robust basis for a
[sqlite] .mode html output with added tag brackets
Hi all,
Short question:
I want to use SQLite's html output option to deliver query results as
a series of HTML rows. But I also want to insert extra HTML tags in
the output. How can this be done?
Longer detail:
I want to use SQLite's HTML savvy conversion of characters (eg an
ampersand '&' becomes HTML as "". But I don't want it to also
convert my tag brackets (eg a left bracket '<' becomes "", but I
want it to stay raw as a bracket '<').
Example:
CREATE TABLE Payroll(name TEXT,age INTEGER,rate REAL);
INSERT INTO Payroll VALUES('Mickey',59,25);
INSERT INTO Payroll VALUES('Donald',54,22.55);
.mode html
SELECT name,'' || age, ' $' || rate
|| '' FROM Payroll;
gives this:
Mickey
P ALIGN=RIGHT>59
P ALIGN=RIGHT>B> $25/B>/P>
Donald
P ALIGN=RIGHT>54
P ALIGN=RIGHT>B> $22.55/B>/P>
But I want to instead get this:
Mickey
59
$25
Donald
54
$22.55
Any solution please?
Thanks,
Tom
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQLite Corruption - Probably Related to auto-vacuum
Check-in [3548] fixes a problem in the pager which can lead to database corruption on a heavily loaded system running autovacuum. I am continuing to analyze the problem in order to fully characterize the circumstances under which database corruption might occur. Once this analysis is complete, you can expect to see the release version 3.3.9 containing the fix. The problem appears to have been in SQLite since version 3.1.0, approximately two years. The corruption behavior is very difficult to reproduce, which explains why it is only just now coming to light. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite performance, locking & threading
Michael, Im not sure that atomic operations would be a suitable alternative. The reason why im using events/conditions is so that the client thread blocks until the server thread has processed the query and returned the result. If i did not need the result then a simple queueing system with atomic operations or critical sections would be fine i guess. The client thread must always block or spin until the server thread has completed the query. Critical sections cant be efficiently used to notify other threads of status change. I did try using critical sections in this way, by spinning until the server thread takes a lock, then blocking and eventually waiting for the server thread to finish. But since there is no way to block the server thread when there is no work to do both the client and server thread must sleep which induces context switching anyway. If you used atomic operations, how would you get the client thread to block and the server thread to block when it is not processing ? Events/conditions seemed to be the best solution, the server thread never runs when it doesnt need to and always wakes up when there is processing to be done. The static initialisation problem occurs becuase the server thread must be running before anything which needs to use it. If you have a static instance of a class which accesses a database and it is initalised before the static instance which controls the server thread, you have a problem. It can be overcome using the initialise on first use idiom, as long as your careful to protect the initalisation with atomic operations, but its still a bit complicated. Emerson On 1/3/07, Michael Ruck <[EMAIL PROTECTED]> wrote: Hi Emerson, Another remark: On Windows using Events synchronization objects involves additional kernel context switches and thus slows you down more than necessary. I'd suggest using a queue, which makes use of the InterlockedXXX operations (I've implemented a number of those, including priority based ones - so this is possible without taking a single lock.) or to use critical sections - those only take the kernel context switch if there really is lock contention. If you can reduce the kernel context switches, you're performance will likely increase drastically. I also don't see the static initialization problem: The queue has to be available before any thread is started. No thread has ownership of the queue, except may be the main thread. Michael -Ursprüngliche Nachricht- Von: Emerson Clarke [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. Januar 2007 00:57 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite performance, locking & threading Nico, I have implemented all three strategies (thead specific connections, single connection multiple threads, and single thread server with multiple client threads). The problem with using thread specific contexts is that you cant have a single global transaction which wraps all of those contexts. So you end up having to use fine grained transactions, which decreases performance. The single connection multiple thread alternative apparently has problems with sqlite3_step being active on more than one thread at the same moment, so cannot easily be used in a safe way. But it is by far the fastest and simplest alternative. The single thread server solution involves message passing between threads, and even when this is done optimally with condition variables (or events on windows) and blocking ive found that it results in a high number of context switches and decreased performance. It does however make a robust basis for a wrapper api, since it guarantees that things will always be synchronised. But using this arrangement can also result in various static initialisation problems, since the single thread server must always be up and running before anything which needs to use it. Emerson On 1/2/07, Nicolas Williams <[EMAIL PROTECTED]> wrote: > On Sat, Dec 30, 2006 at 03:34:01PM +, Emerson Clarke wrote: > > Technically sqlite is not thread safe. [...] > > Solaris man pages describe APIs with requirements like SQLite's as > "MT-Safe with exceptions" and the exceptions are listed in the man page. > > That's still MT-Safe, but the caller has to play by certain rules. > > Anyways, this is silly. SQLite API is MT-Safe with one exception and > that exception is rather ordinary, common to other APIs like it that > have a context object of some sort (e.g., the MIT krb5 API), and not > really a burden to the caller. In exchange for this exception you get > an implementation of the API that is lighter weight and easier to > maintain than it would have been without that exception; a good > trade-off IMO. > > Coping with this exception is easy. For example, if you have a server > app with multiple worker threads each of which needs a db context then > you could use a thread-specific key to track a per-thread db context; > use pthread_key_create(3C) to create the key,
Re: [sqlite] errors @ "./testfixture conflict.test"
comparing with apples' darwin-bundled sqlite src for an older sqlite version (313 was it?), the addition of, -DASSERT_VIA_CALLBACK=1 -DENABLE_LOCKING_CALLBACKS=1 as CFLAGS _seems_ to do the trick. after applying the patch discussed earlier, then, cd /projects/sqlite-3.3.8/configure aclocal glibtoolize --force --copy autoconf -f unsetenv CFLAGS CPPFLAGS CXX CXXFLAGS LDFLAGS LDDLFLAGS LD_PREBIND EXTRA_LDFLAGS EXTRA_LIBS LC_ALL LANG LINGUAS setenv CFLAGS "-DSQLITE_DEBUG=1 -DSQLITE_MEMDEBUG=1 -DASSERT_VIA_CALLBACK=1 -DENABLE_LOCKING_CALLBACKS=1" setenv LDFLAGS "-L/usr/local/lib -lreadline -L/usr/local/ncurses/lib -lncurses" setenv CPPFLAGS "-no-cpp-precomp -I/usr/local/include/readline -I/usr/local/ncurses/include" setenv LD_TWOLEVEL_NAMESPACE 1 rm -rf sqlite_build mkdir sqlite_build cd sqlite_build ../sqlite-3.3.8/configure \ --prefix=/usr/local/sqlite \ --enable-debug \ --enable-tcl \ --enable-shared \ --enable-static \ --enable-threadsafe=no my build is OK, sqlite3 -version 3.3.8 and eventual make fulltest passes 100% of tests! Thread-specific data deallocated properly 0 errors out of 316139 tests Failures on these tests: excellent thanks! - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] undefined reference to 'sqlite3_extension_init' (compile error)
I am using http://sqlite.org/sqlite-source-3_3_8.zip and mingw gcc and gnu make. v. 3.3.5 worked fine, but with 3.3.8 (3.3.6 introduced the extention thingy) i have problems to compile it, error output: [LD] output-i386\dll\3rdparty\sqlite3\sqlite3.dll sqlite3.temp.exp:fake:(.edata+0x114): undefined reference to 'sqlite3_extension_ init' dll baseaddress: 0x6090 My defines related to SQLite3: NO_TCL OS_WIN=1 OS_MAC=0 OS_UNIX=0 DTHREADSAFE=1 SQLITE_CORE SQLITE_ENABLE_FTS1 SQLITE_EXTENSION_INIT1 SQLITE_EXTENSION_INIT2 How can I fix that issue? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Why is the sqlite3.def file missing in v. 3.3.8 package ??? (SQLite3 source code preprocessed for Win32)
An addition to my other email: I have just found that it is listed twice in the official SQLite Ticket/Bug-System: http://www.sqlite.org/cvstrac/tktview?tn=2031 http://www.sqlite.org/cvstrac/tktview?tn=2059 Please fix this issue(s), as we from the Win32 user land would like to use SQLite3 in up-to-date version too. I know that v. 3.3.8 "only" added for most user minor things and FTS1 will never be useable for real things, I will update to v. 3.3.7 for now and wait for a later version which come with FTS2 Hopefully FTS2 will come with grouping operator (i.e. parentheses) as only hen full-text search will become really useful. Best regards, Klemens Friedl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Why is the sqlite3.def file missing in v. 3.3.8 package ??? (SQLite3 source code preprocessed for Win32)
Klemens Friedl <[EMAIL PROTECTED]> wrote: All SQLite3 Source code package (preprocessing for Win32) except the latest one (v. 3.3.8) have come with "sqlite3.def" file which I used to build sqlite3.dll (dynamic link library). Why hasn't the "sqlite3.def" file been added? http://sqlite.org/sqlite-source-3_3_8.zip ... no sqlite3.def It's in http://sqlite.org/sqlitedll-3_3_8.zip Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Why is the sqlite3.def file missing in v. 3.3.8 package ??? (SQLite3 source code preprocessed for Win32)
All SQLite3 Source code package (preprocessing for Win32) except the latest one (v. 3.3.8) have come with "sqlite3.def" file which I used to build sqlite3.dll (dynamic link library). Why hasn't the "sqlite3.def" file been added? http://sqlite.org/sqlite-source-3_3_8.zip ... no sqlite3.def All other versions have the "sqlite3.def" file: http://sqlite.org/sqlite-source-3_3_7.zip http://sqlite.org/sqlite-source-3_3_6.zip http://sqlite.org/sqlite-source-3_3_5.zip ... It may be because of FTS 1, but why not just add two "def" files? Can someone provide me the def file so that I can update (& build) my source to v. 3.3.8 ? And please provide the "def" file(s) again with v. 3.3.9, thanks! Best regards, Klemens Friedl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Corruption - Probably Related to auto-vacuum
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > "Ron Avriel" <[EMAIL PROTECTED]> wrote: > > > > > > >If you only run a single process at a time does the problem go > > > >away. (If it does, that indicates that the problem is in the > > > >locking code - an area where Fedora has given us no end of problems > > > >in the past - not in the BTree layer.) > > > > > > I believe the problem is a very subtle locking problem during > > > auto-vacuum. > > > > In pager.c near line 33 is an "#if 0" which if you change to "#if 1" > > will turn on debugging printfs in the pager module. Please do this > > and send me the output on a run that fails. > > Is the patch http://www.sqlite.org/cvstrac/chngview?cn=3547 related to > this autovaccum issue? > I thought it might be, but it didn't fix the problem. (I'm in private communication with Ron - we've spent a lot of time on this.) I'll let you know if and when we find and fix the bug. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] errors @ "./testfixture conflict.test"
snowcrash+sqlite <[EMAIL PROTECTED]> wrote:
> whittling down the last few testsuite errors,
>
> % ./testfixture ../sqlite-3.3.8/test/conflict.test
>
>
> the tests that FAIL, all have config param "t0 == 0", where,
>
>
> i _think_ that's the "what" in this error.
>
> now, the "why" ...
>
> ideas?
>
Recompile testfixture with debugging (-g) turned on. Then modify
the conflict.test script as follows:
*** conflict.test 17 Jan 2006 09:35:02 - 1.27
--- conflict.test 3 Jan 2007 12:29:28 -
***
*** 309,314
--- 309,315
if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
execsql {pragma temp_store=file}
set ::sqlite_opentemp_count 0
+ if {i==2} btree_breakpoint
set r0 [catch {execsql [subst {
DROP TABLE t1;
CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
Bring up your debugger (gdb?) and set a breakpoint on the C
routine "btree_breakpoint". Run the conflict.test script. The
script will stop just before running test 6.2. Then set a breakpoint
on the function sqlite3pager_opentemp. Continue until this second
breakpoing is hit. Now single-step through sqlite3pager_opentemp
and figure out why the sqlite3_opentemp_count variable is not
being incremented. If it is being incremented, figure out why
this variable is somehow different from the sqlite3_opentemp_count
variable found in test1.c. Might be some kind of strange linker
problem
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] [RESOLVED/PATCH] re: "Error: no such function: randstr" @ v3.3.8 on OSX
snowcrash+sqlite <[EMAIL PROTECTED]> wrote: > > > well, a 0.04% test failure rate ain't bad! > > > > > > > Are you, perchance, running this on a network filesystem of > > some kind? > > nope. all on my 'local' box. > > just starting to look over these ... > > i had NOT *defined* UTF8 as the encoding of choice ... or, at all, > actually. so, iirc, that means defaault to ISO8859, yes? > > wondering if the utf16* errors require UTF8? > That encoding thing in the Makefile is a holdover from sqlite version 2. It is no longer used. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] sqlite performance, locking & threading
Hi Emerson, Another remark: On Windows using Events synchronization objects involves additional kernel context switches and thus slows you down more than necessary. I'd suggest using a queue, which makes use of the InterlockedXXX operations (I've implemented a number of those, including priority based ones - so this is possible without taking a single lock.) or to use critical sections - those only take the kernel context switch if there really is lock contention. If you can reduce the kernel context switches, you're performance will likely increase drastically. I also don't see the static initialization problem: The queue has to be available before any thread is started. No thread has ownership of the queue, except may be the main thread. Michael -Ursprüngliche Nachricht- Von: Emerson Clarke [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. Januar 2007 00:57 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite performance, locking & threading Nico, I have implemented all three strategies (thead specific connections, single connection multiple threads, and single thread server with multiple client threads). The problem with using thread specific contexts is that you cant have a single global transaction which wraps all of those contexts. So you end up having to use fine grained transactions, which decreases performance. The single connection multiple thread alternative apparently has problems with sqlite3_step being active on more than one thread at the same moment, so cannot easily be used in a safe way. But it is by far the fastest and simplest alternative. The single thread server solution involves message passing between threads, and even when this is done optimally with condition variables (or events on windows) and blocking ive found that it results in a high number of context switches and decreased performance. It does however make a robust basis for a wrapper api, since it guarantees that things will always be synchronised. But using this arrangement can also result in various static initialisation problems, since the single thread server must always be up and running before anything which needs to use it. Emerson On 1/2/07, Nicolas Williams <[EMAIL PROTECTED]> wrote: > On Sat, Dec 30, 2006 at 03:34:01PM +, Emerson Clarke wrote: > > Technically sqlite is not thread safe. [...] > > Solaris man pages describe APIs with requirements like SQLite's as > "MT-Safe with exceptions" and the exceptions are listed in the man page. > > That's still MT-Safe, but the caller has to play by certain rules. > > Anyways, this is silly. SQLite API is MT-Safe with one exception and > that exception is rather ordinary, common to other APIs like it that > have a context object of some sort (e.g., the MIT krb5 API), and not > really a burden to the caller. In exchange for this exception you get > an implementation of the API that is lighter weight and easier to > maintain than it would have been without that exception; a good > trade-off IMO. > > Coping with this exception is easy. For example, if you have a server > app with multiple worker threads each of which needs a db context then > you could use a thread-specific key to track a per-thread db context; > use pthread_key_create(3C) to create the key, pthread_setspecific(3C) > once per-thread to associate a new db context with the calling thread, > and pthread_getspecific(3C) to get the calling thread's db context > when you need it. If you have a protocol where you have to step a > statement over multiple message exchanges with a client, and you don't > want to have per-client threads then get a db context > per-client/exchange and store that and a mutext in an object that > represents that client/exchange. And so on. > > Nico > -- > > -- > --- To unsubscribe, send email to > [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] R: [sqlite] Cross compile error!
OK Richard, I've follow your steps and all is fine. I've created libsqlite3.a, but how i create .so files? Another questionwhen i compile a program using cross compile and the libsqlite3.a lib, the executable file (in my case .cgi file) is too big!! Why? If i strip it the size decrease a bit. My compile command is : mipsel-linux-gcc -o xxx.cgi xxx.c -lsqlite3 Mystrip command is: mipsel-linux-strip xxx.cgi Thank a lot. Regards -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato: martedì 2 gennaio 2007 17.25 A: sqlite-users@sqlite.org Oggetto: Re: [sqlite] Cross compile error! "Francesco Andrisani" <[EMAIL PROTECTED]> wrote: > Hi comunity, > i've a problem when i try to cross compile sqlite-3.3.8 for mipsel > architecture. Suggested approach. 1. Configure for the host. 2. Type "make target_source". This puts a bugs of C source files in a subdirectory named "tsrc". 3. Remove tclsqlite.c and possibly also shell.c from tsrc. 4. Compile all the other C files in tsrc using your cross-compiler. for i in *.c; do xcc -c $i.c; done ar r libsqlite3.a *.o The key step is to do "make target_source" on your host machine. There is a lot of generated code in SQLite. That step will do all the automatic code generation and leave you with a batch of ordinary C source code files, which are much easier to deal with using a cross-compiler. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
