Re: [sqlite] SQLite3 and threading
> I cite from http://sqlite.org/faq.html#q6: > > "The restriction on moving database connections across threads was > relaxed somewhat in version 3.3.1. With that and subsequent versions, it > is safe to move a connection handle across threads as long as the > connection is not holding any fcntl() locks. You can safely assume that > no locks are being held if no transaction is pending and all statements > have been finalized." I can't say why this was written and what does that mean. But I had an application that moved all SQLite handles across threads and did that with open transactions too. And it had no problems. IIRC, there were examples of such behavior on this list too, also no problems were reported. So maybe this is really old stuff. Pavel On Thu, Feb 3, 2011 at 1:59 PM, Ulrich Telle wrote: > Am 03.02.2011 15:53, schrieb Pavel Ivanov: >> It seems that this explanation as well as all other statements in the >> thread you linked are coming from the wrong assumption that SQLite's >> handles cannot be used from any thread other than the one created that >> handle. > > The explanation I gave to Stefano is *not* based on the assumption that > SQlite handles can't be passed from one thread to another. But certainly > passing them around *can* lead to problems. > >> Although this was true in some earlier versions of SQLite it's >> not true in the current version. So if SQLite is compiled with >> THREADSAFE=1 (as mentioned in that thread) then you can do with it >> whatever you want. Just beware of possible data races and potentially >> uncommitted transactions because of some open statement handles. And >> if as you say there's no simultaneous access to the database from >> different threads then there's no difference in your usage pattern >> from single-threaded one. > > I cite from http://sqlite.org/faq.html#q6: > > "The restriction on moving database connections across threads was > relaxed somewhat in version 3.3.1. With that and subsequent versions, it > is safe to move a connection handle across threads as long as the > connection is not holding any fcntl() locks. You can safely assume that > no locks are being held if no transaction is pending and all statements > have been finalized." > > The problem is that Stefano wants to pass a result set from one thread > to another. The result set has an associated SQLite statement handle > which is inherently *not* finalized. And that might cause trouble. > > If access to the database handle is serialized as Stefano claims passing > the statement handle between threads shouldn't cause problems, true, but > in that case I don't see the advantages of having a separate thread over > using a global database access instance. > > Regards, > > Ulrich > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Auto Reply: Auto Reply: Re: sqlite-users Digest, Vol 38, Issue 4
This is an auto-replied message. I have left Oracle. My new e-mail address is n...@cryptonector.com. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Auto Reply: Re: sqlite-users Digest, Vol 38, Issue 4
This is an auto-replied message. I have left Oracle. My new e-mail address is n...@cryptonector.com. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 38, Issue 4
On 2/4/2011 11:14 AM, Scott Baker wrote: > On 02/04/2011 04:00 AM, sqlite-users-requ...@sqlite.org wrote: >> On 2/3/2011 12:10 PM, Scott Baker wrote: #3) Query for customers who bought exactly 2 apples? >> select CustomerID from Customers >> group by CustomerID >> having sum(Type = 'Apple') = 2; > > I thought of one other case that I couldn't figure out. How would I get > a count (instead of a list) of all the customers that bought exactly two > apples? select count(*) from ( select CustomerID from Customers group by CustomerID having sum(Type = 'Apple') = 2; ) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 38, Issue 4
On 02/04/2011 04:00 AM, sqlite-users-requ...@sqlite.org wrote: > On 2/3/2011 12:10 PM, Scott Baker wrote: >> > CREATE Table Customers ( >> >EntryID INTEGER PRIMARY KEY, >> >CustomerID INT, >> >Type ENUM >> > ); >> > >> > #1) Query for customers who*ONLY* bought apples > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple')>0 and sum(Type != 'Apple')=0; > >> > #2) Query for customers who bought apples*AND* bananas > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple')>0 and sum(Type = 'Banana')>0; > >> > #3) Query for customers who bought exactly 2 apples? > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple') = 2; > > -- or > > select CustomerID from Customers > where Type = 'Apple' > group by CustomerID > having count(*) = 2; This is exactly what I needed thank you so much! I thought of one other case that I couldn't figure out. How would I get a count (instead of a list) of all the customers that bought exactly two apples? I spent about 45 minutes hacking on the above but I couldn't get it. I'm thinking that would have to be a subselect? -- Scott Baker - Canby Telcom System Administrator - RHCE - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger for incrementing a column is slow
On Fri, Feb 4, 2011 at 7:26 AM, Simon Slavin wrote: > > On 4 Feb 2011, at 9:42am, Dan Kennedy wrote: > > > The problem is that the trigger version is creating a statement journal > > for each INSERT statement. It doesn't *really* need to, as there is no > > way that this statement/trigger can hit a constraint after modifying > > any rows. However at the moment I think SQLite opens a statement > > transaction for any statement that will fire one or more triggers. > > I'm not quite up with the terminology but now I'm interested. > > First, do you feel that this behaviour is a bug and can be addressed in > future versions ? > I strive to be more precise and reserve the word "bug" for cases where the software gets the wrong answer. When the correct answer is obtained, just more slowly than one hopes, that is not a "bug" but a "performance enhancement opportunity." We will look into taking advantage of this performance enhancement opportunity to make SQLite run faster in a future release. But we have to proceed carefully here. Performance enhancements are our #1 source of bugs, since performance enhancements typically result in more complex code, and complication tends to lead to bugs. Our first priority is to avoid bugs. It is seldom helpful to get the wrong answer quickly. > Second, is this the same as the automatic creation of transactions if the > programmer doesn't create a transaction ? In other words, if the programmer > doesn't do a BEGIN, does SQLite make one transaction for the INSERT, and > another for each action inside anything that triggers ? Or do all the > actions triggered by the INSERT get included within the same automatic > transaction ? > When a statement is one of several statements that occur in the middle of a larger BEGIN...END and that statement might fail, we have to open a separate journal that can be used to rollback partial results of that one statement without having to rollback the entire transaction. The statement journal is used for that purpose. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger for incrementing a column is slow
On 4 Feb 2011, at 9:42am, Dan Kennedy wrote: > The problem is that the trigger version is creating a statement journal > for each INSERT statement. It doesn't *really* need to, as there is no > way that this statement/trigger can hit a constraint after modifying > any rows. However at the moment I think SQLite opens a statement > transaction for any statement that will fire one or more triggers. I'm not quite up with the terminology but now I'm interested. First, do you feel that this behaviour is a bug and can be addressed in future versions ? Second, is this the same as the automatic creation of transactions if the programmer doesn't create a transaction ? In other words, if the programmer doesn't do a BEGIN, does SQLite make one transaction for the INSERT, and another for each action inside anything that triggers ? Or do all the actions triggered by the INSERT get included within the same automatic transaction ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multithreading problem
On 02/04/2011 01:04 AM, Tiberio, Sylvain wrote: > You're right! > > I checked the Solaris documentation the correct flag to compile > multithread program is -D_REENTRANT. > > I have reconfigured and remade sqlite libs: >./configure --enable-threadsafe CFLAGS=-D_REENTRANT >make > > I have remade my test file ...and it is working well now! > Thanks you very much!!! > > Another question: Why ./configure doesn't set itself this flag when I > use --enable-threadsafe in a Solaris system? A bug I suppose. Will fix it for 3.7.6. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger for incrementing a column is slow
On 02/04/2011 03:24 PM, Kevin Wojniak wrote: > > On Feb 3, 2011, at 10:35 PM, Dan Kennedy wrote: > >> Do you have a test program that we can use to reproduce this phenomenon? >> >> Dan. > > Here is a complete program: > http://pastie.org/pastes/1527560 > > Set USE_TRIGGER to see the trigger version. Thanks for this. The problem is that the trigger version is creating a statement journal for each INSERT statement. It doesn't *really* need to, as there is no way that this statement/trigger can hit a constraint after modifying any rows. However at the moment I think SQLite opens a statement transaction for any statement that will fire one or more triggers. Statement journals: http://www.sqlite.org/tempfiles.html#stmtjrnl The effect is that when using the no-trigger version, all that most of your INSERT and UPDATE statements have to do is modify the database within the cache. However the trigger version has to copy the original page data into the statement journal before it can modify them. If the statement journal is stored in a temporary file, this means many calls to write(). You can improve the situation some by using an in-memory statement journal: PRAGMA temp_store = memory; But the trigger version is still slower. Because of the statement transaction SQLite is opening. Sub-optimal, that. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] beginner question: help required to retrieve "filename" from an open "sqlite3*" handle
On 4 February 2011 07:38, Andreas Otto wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > > with "sqlite2_open" the parameter "filename" is used to specify the > database location. > > Q: how I can retrieve this parameter from an open handle? http://www.sqlite.org/pragma.html#pragma_database_list > > sqlite-version: latest > > > mfg, Andreas Otto Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger for incrementing a column is slow
On Feb 3, 2011, at 10:35 PM, Dan Kennedy wrote: > Do you have a test program that we can use to reproduce this phenomenon? > > Dan. Here is a complete program: http://pastie.org/pastes/1527560 Set USE_TRIGGER to see the trigger version. Thanks, Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users