[sqlite] WAL and locking_mode EXCLUSIVE

Hello, I'm confused by the effects of WAL on the EXCLUSIVE locking mode. The following works just fine (example uses the apsw Python interface, but it's the same when using the C API): import apsw conn1 = apsw.Connection('barf.db') cur1 = conn1.cursor() cur1.execute('create table foo (bar int)')

[sqlite] Auto-Checkpoint when Transaction finishes?

Hello, About two years I posted an example that results in unbounded growth of the WAL file (http://news.gmane.org/find-root.php?message_id=%3c87r57xrq1m.fsf%40inspiron.ap.columbia.edu%3e). At that point, the conclusion was that it might be worth to consider auto-checkpointing when a read

Re: [sqlite] Sorting by rowid needs temp b-tree?

"Igor Tandetnik" <itandetnik-fh9draxc...@public.gmane.org> writes: > Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> wrote: >> Hello, >> >> sqlite> explain query plan SELECT name_id, inode, rowid FROM >> contents WHERE parent_inode=42 AND

[sqlite] Sorting by rowid needs temp b-tree?

Hello, sqlite> explain query plan SELECT name_id, inode, rowid FROM contents WHERE parent_inode=42 AND rowid > 12932 ORDER BY rowid; 0|0|0|SEARCH TABLE contents USING INDEX sqlite_autoindex_contents_1 (parent_inode=?) (~6 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY Why does ordering by rowid need

[sqlite] Consequences of resetting sqlite_sequence.seq

Hello, I would like to store rows with an id column that is unique (at least most of the time, see below), but must be below some maximum value, and I would like to do so in a very space efficient way. My idea is to use the rowid as the id column and declare it as autoincrement. After every

[sqlite] Index usage when querying views

Hello, I have two tables, inodes and inode_blocks, which have indices on id and inode respectively. The following query seems to make optimal use of the indices: sqlite> explain query plan SELECT 1 from inode_blocks where inode=42 UNION SELECT 1 from inodes where

[sqlite] Auto-checkpoint when read transaction finishes? (was: Bug or not: WAL file is not auto-checkpointed when transaction finishes)

Richard Hipp <drh-czdrofg0bjidnm+yrof...@public.gmane.org> writes: > On Tue, May 17, 2011 at 5:11 PM, Nikolaus Rath > <nikolaus-bth8mxji...@public.gmane.org> wrote: >> Please consider the attached testcase. The WAL file should grow to at >> most 1 MB. This limit i

[sqlite] Bug or not: WAL file is not auto-checkpointed when transaction finishes

Hi, Please consider the attached testcase. The WAL file should grow to at most 1 MB. This limit is exceeded, because the script adds data while a second cursor has an active SELECT query. However, when the SELECT query finishes, the WAL file is not auto-checkpointed either. An access pattern

Re: [sqlite] No journal vs WAL journal

Nikolaus Rath <nikol...@rath.org> writes: [ WAL Performance ] > Really no one an idea of why this is happening? I tried to investigate this further myself and created a small example program (attached). It fills a table with 500,000 entries without using explicit transactions. The final

Re: [sqlite] No journal vs WAL journal

Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> writes: > I'm experimenting with creation of a 156 MB database from scratch. I've > set synchronous = NORMAL and locking_mode = exlusive. > > With journal_mode = off and without wrapping the INSERTs into a > transaction,

Re: [sqlite] No journal vs WAL journal

Simon Slavin <slavins-drl2ohjjk6tg9huczpv...@public.gmane.org> writes: > On 6 Apr 2011, at 5:06pm, Nikolaus Rath wrote: > >> Another question though: what is the recommended way to insert lots >> of data with WAL enabled? Without a transaction, the WAL file stays >>

Re: [sqlite] No journal vs WAL journal

Pavel Ivanov writes: >> Furthermore, if I turn off auto checkpointing, the WAL file grows to >> more than 5 GB without transactions, but only to about 922 MB with a >> transaction. Are the commit markers really taking that much space? > >

Re: [sqlite] No journal vs WAL journal

Igor Tandetnik <itandetnik-fh9draxc...@public.gmane.org> writes: > On 4/5/2011 10:01 PM, Nikolaus Rath wrote: >> I'm experimenting with creation of a 156 MB database from scratch. I've >> set synchronous = NORMAL and locking_mode = exlusive. >> >> With journa

[sqlite] No journal vs WAL journal

Hello, I'm experimenting with creation of a 156 MB database from scratch. I've set synchronous = NORMAL and locking_mode = exlusive. With journal_mode = off and without wrapping the INSERTs into a transaction, creating the db takes 54 seconds. With journal_mode = WAL (and still no transaction),

Re: [sqlite] UPDATE during SELECT

"Igor Tandetnik" <itandetnik-fh9draxc...@public.gmane.org> writes: > Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> wrote: >> I understand that running INSERT or DELETE during an active SELECT query >> can get me into trouble. But is it safe to

[sqlite] UPDATE during SELECT

Hello, I understand that running INSERT or DELETE during an active SELECT query can get me into trouble. But is it safe to run (in pseudocode): for value in "SELECT main_column IN mytable": UPDATE mytable SET other_column='foobar' WHERE main_column=value ? Thanks, -Nikolaus --

Re: [sqlite] Why the deadlock?

Dan Kennedy writes: > On Aug 25, 2010, at 10:40 PM, Pavel Ivanov wrote: > >> Nikolaus, >> >> I've traced your application a bit (with SQLite 3.6.18 sources) and >> it looks like SQLite does some nasty thing nobody in this thread >> expected.

Re: [sqlite] Why the deadlock?

Doug Currie <doug.currie-re5jqeeqqe8avxtiumw...@public.gmane.org> writes: > On Aug 24, 2010, at 10:57 AM, Nikolaus Rath wrote: > >> Nikolaus Rath >> <nikolaus-bth8mxji4b0-xmd5yjdbdmrexy1tmh2...@public.gmane.org> >> writes: >>> Still no one abl

Re: [sqlite] Why the deadlock?

Hi, I only saw http://article.gmane.org/gmane.comp.db.sqlite.general/58835, was there anything else? -Nikolaus Gerry Snyder <mesmerizerfan-re5jqeeqqe8avxtiumw...@public.gmane.org> writes: > Er, did you not see Dan Kennedy's comments a fed days ago?? > > On 8/24/10, Nikolaus

Re: [sqlite] Why the deadlock?

Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> writes: > Still no one able to clarify the issues raised in this thread? > > Let me try to summarize what I still don't understand: > > - Will SQLite acquire and release an EXCLUSIVE lock while keeping a >SHA

Re: [sqlite] Why the deadlock?

Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> writes: > Dan Kennedy <danielk1977-re5jqeeqqe8avxtiumw...@public.gmane.org> writes: >> On Aug 17, 2010, at 1:48 AM, Nikolaus Rath wrote: >> >>> Hello, >>> >>> The script below fails with >

Re: [sqlite] NOT NULL asymetry between CREATE TABLE and ALTER TABLE, why?

Michael Schlenker writes: > Hi all, > > Have a look at the following short sqlite shell session: > > SQLite version 3.6.4 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table foo (a text(5) NOT NULL); >

Re: [sqlite] Why the deadlock?

Dan Kennedy <danielk1977-re5jqeeqqe8avxtiumw...@public.gmane.org> writes: > On Aug 17, 2010, at 1:48 AM, Nikolaus Rath wrote: > >> Hello, >> >> The script below fails with >> >> Deadlock detected when executing 'DELETE FROM foo WHERE id=2' >

Re: [sqlite] Why the deadlock?

"Igor Tandetnik" <itandetnik-fh9draxc...@public.gmane.org> writes: > Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> wrote: >> What I think should be happening instead is this: >> >> - When executing statement 1, the main thread obtains a SHARE

Re: [sqlite] Why the deadlock?

Dan Kennedy <danielk1977-re5jqeeqqe8avxtiumw...@public.gmane.org> writes: > On Aug 17, 2010, at 1:48 AM, Nikolaus Rath wrote: > >> Hello, >> >> The script below fails with >> >> Deadlock detected when executing 'DELETE FROM foo WHERE id=2' >

[sqlite] Why the deadlock?

Hello, The script below fails with Deadlock detected when executing 'DELETE FROM foo WHERE id=2' What I think should be happening instead is this: - When executing statement 1, the main thread obtains a SHARED lock. - When executing statement 2, the main thread briefly obtains an

[sqlite] VACUUM and WAL

Hi, Could someone clarify to me how the VACUUM command works if WAL is enabled? I would like to compact my database, but I am note sure if I should: 1) Run PRAGMA wal_checkpoint to get all outstanding commits into the database file and then VACUUM to compact the database file, or should I

Re: [sqlite] crypt() as SQL core function

Roger Binns writes: >> I was wondering why there isn't a hash function build into SQLite. > > Firstly note the last 4 letters of the name. It is indeed a fairly frequent > lament here that SQLite should include some functions the poster thinks is > generally applicable.

Re: [sqlite] crypt() as SQL core function

Roger Binns <rog...@rogerbinns.com> writes: > On 07/31/2010 07:28 PM, Nikolaus Rath wrote: >> Possible use case: I want to send someone an SQLite database with my >> data, so that he can debug an application that's using the database >> (since it's having problems with

Re: [sqlite] crypt() as SQL core function

"Jay A. Kreibich" <j...@kreibi.ch> writes: > On Sat, Jul 31, 2010 at 01:12:36PM -0400, Nikolaus Rath scratched on the wall: >> >> When tracing down bugs in an application that uses SQLite, I have >> repeatedly faced the problem that I wanted to send (or

Re: [sqlite] crypt() as SQL core function

Simon Slavin writes: >> Please don't suggest other ways of achieving this. There are plenty and >> I am able to use them. My question is about the pros and cons of adding >> a one-way hash function to SQLite (maybe calling it crypt() was >> misleading for some). > > That's

Re: [sqlite] crypt() as SQL core function

Simon Slavin <slav...@bigfraud.org> writes: > On 31 Jul 2010, at 6:12pm, Nikolaus Rath wrote: > >> When tracing down bugs in an application that uses SQLite, I have >> repeatedly faced the problem that I wanted to send (or get) a copy of >> the sqlite database

Re: [sqlite] crypt() as SQL core function

Alexey Pechnikov <pechni...@mobigroup.ru> writes: > Nikolaus Rath, see encfs/gnupg/truecrypt/compressed filesystems and > similar solutions. Sorry, but you missed the point entirely. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprin

[sqlite] crypt() as SQL core function

Hello, When tracing down bugs in an application that uses SQLite, I have repeatedly faced the problem that I wanted to send (or get) a copy of the sqlite database without disclosing potentially sensitive contents. I think it would be fantastic if SQLite had a build-in crypt() function, so that I

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

Pavel Ivanov writes: >>> Yeah, I'm around. I don't know what an "exact disassembly" is or how to >>> provide one, but if someone tells me what to do then I'm most likely >>> willing to do it. > > Nikolaus, you can do it like this: >> gdb your_application > (gdb) disassemble

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

Roger Binns <rog...@rogerbinns.com> writes: > On 05/13/2010 06:43 AM, Nikolaus Rath wrote: >> Yeah, I'm around. I don't know what an "exact disassembly" is or how to >> provide one, but if someone tells me what to do then I'm most likely >> willing to do it

Re: [sqlite] Incorrect assertion in mutexes plus pcache1Alloc

Roger Binns writes: >> Although I see that it can return false in this case if another thread >> is inside pthreadMutexEnter and optimizer has switched order of >> assignment of the values to owner and nRef inside pthreadMutexEnter >> (it is allowed to do so because

[sqlite] Install python-apsw under different name?

Hello, I am compiling apsw with SQLITE_32BIT_ROWID by adding the #define to sqlite3/sqlite3config.h after running 'setup.py fetch --sqlite'. However, as I understood, the resulting apsw will not work with normal, 64-bit rowid databases. To prevent other applications from accidentally using the

Re: [sqlite] Reduce database file size

Roger Binns <rog...@rogerbinns.com> writes: > On 04/11/2010 11:09 AM, Nikolaus Rath wrote: >> Yes, you remember correctly. Actually that would be a perfect solution. >> But how do I use it? It seems to me that I need to pass some argument to >> Shell.command_dump(),

Re: [sqlite] Reduce database file size

Roger Binns <rog...@rogerbinns.com> writes: > On 04/10/2010 03:06 PM, Nikolaus Rath wrote: >> However, I noticed that if I dump the entire database into a text file >> with the SQLite shell and then compress the text file, the result is >> significantly smaller tha

[sqlite] Reduce database file size

Hello, I would like to make an SQLite database as small as possible to transfer it over a slow link. Currently I am dropping all custom indices, run VACUUM and compress the file with lzma -9. However, I noticed that if I dump the entire database into a text file with the SQLite shell and then

Re: [sqlite] Reasons for SQLITE_CANTOPEN

"D. Richard Hipp" <d...@hwaci.com> writes: > On Jan 12, 2010, at 6:58 AM, Ian Jackson wrote: > >> Nikolaus Rath writes ("Re: [sqlite] Reasons for SQLITE_CANTOPEN"): >>> Edzard Pasma <pasm...@concepts.nl> writes: >>>> Hope strace

Re: [sqlite] Unexplained "disk i/o error", Unix

Ian Jackson writes: > I wrote: >> I have recently had an apparently isolated failure of a program >> making some updates to a sqlite database. The only information I >> have is this error message: >> >> DBD::SQLite::st execute failed: disk I/O error(10) at

Re: [sqlite] Reasons for SQLITE_CANTOPEN

Edzard Pasma writes: >> When my program has been running for a while, I suddenly get an >> SQLITE_CANTOPEN error when I'm trying to open a database >> connection with a new thread. The database file, however, is >> definitively present and accessible. > > Hope

Re: [sqlite] Reasons for SQLITE_CANTOPEN

Edzard Pasma <pasm...@concepts.nl> writes: > Op 10-jan-2010, om 19:25 heeft Nikolaus Rath het volgende geschreven: > >> Filip Navara <filip.nav...@gmail.com> writes: >>>> I am accessing the same database from several threads, each using a >>>>

Re: [sqlite] Reasons for SQLITE_CANTOPEN

Filip Navara writes: >> I am accessing the same database from several threads, each using a >> separate connection. Shared cache is not enabled. >> >> When my program has been running for a while, I suddenly get an >> SQLITE_CANTOPEN error when I'm trying to open a

[sqlite] Reasons for SQLITE_CANTOPEN

Hello, I am accessing the same database from several threads, each using a separate connection. Shared cache is not enabled. When my program has been running for a while, I suddenly get an SQLITE_CANTOPEN error when I'm trying to open a database connection with a new thread. The database file,

Re: [sqlite] Unique column constrained to 0 .. 2^32?

Scott Hess writes: >> Hello, >> >> I need a unique value in the range 0 to 2^32 for every row in my table. >> What is the best way to implement this in SQLite? >> >> I suppose that for the first 2^32 inserts I could just use the >> rowid, but after that the rowid might increase

[sqlite] Unique column constrained to 0 .. 2^32?

Hello, I need a unique value in the range 0 to 2^32 for every row in my table. What is the best way to implement this in SQLite? I suppose that for the first 2^32 inserts I could just use the rowid, but after that the rowid might increase above 2^32 while my desired unique value should look for

Re: [sqlite] Add IMMEDIATE/EXCLUSIVE support to SAVEPOINT?

"D. Richard Hipp" <d...@hwaci.com> writes: > On Dec 4, 2009, at 9:45 PM, Nikolaus Rath wrote: >> >> Has it ever been discussed (or for good reason never been considered) >> to extent the SAVEPOINT command with respect to its behavior if there >>

[sqlite] Add IMMEDIATE/EXCLUSIVE support to SAVEPOINT?

Hello, Has it ever been discussed (or for good reason never been considered) to extent the SAVEPOINT command with respect to its behavior if there is no enclosing transaction? At the moment I am manually keeping track of enclosing transactions and then switch dynamically between "SAVEPOINT" and

[sqlite] The point of BEGIN DEFERRED

Hello, If I understand the documentation right, the following SQL snippet can cause deadlocks when running multi-threaded, because both threads can acquire and hold the SHARED lock at the same time, but then no thread can get the EXCLUSIVE lock anymore (note that the SELECT and INSERT are meant

Re: [sqlite] UPDATE 11 times slower than SELECT?

Roger Binns <rog...@rogerbinns.com> writes: > Nikolaus Rath wrote: >> Is it normal that UPDATE still takes more than 11 times as much time as >> SELECT, or should I be able to get better performance? > > The UPDATE time also includes the time waiting to acquire

Re: [sqlite] UPDATE 11 times slower than SELECT?

"Igor Tandetnik" <itandet...@mvps.org> writes: > Nikolaus Rath wrote: >> When running apswtrace on my Python program, I got the following >> output: >> >> LONGEST RUNNING - AGGREGATE >> >> 16638 11.041 UPDATE inodes SET size=MAX(size,?), ct

[sqlite] UPDATE 11 times slower than SELECT?

Hi, When running apswtrace on my Python program, I got the following output: LONGEST RUNNING - AGGREGATE 16638 11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=? WHERE id=? 16638 0.938 SELECT s3key FROM inode_s3key WHERE inode=? AND offset=? i.e. both statements were executed

Re: [sqlite] Busy Handler in Shared Cache Mode?

wait-time is not necessarily > a worse performance. By the way, this was measured on a > single-processor system. > > Hope this is useful to know. > > Best regards, Edzard. > > On Fri, Aug 7, 2009 at 1:45 PM, Nikolaus > Rath<nikol...@rath.org> wrote: >> Hello, >>

Re: [sqlite] Busy Handler in Shared Cache Mode?

Pavel Ivanov writes: > Maybe you could benefit of using sqlite3_unlock_notify()? See more > info here: http://www.sqlite.org/c3ref/unlock_notify.html. Yeah, that seems to be exactly what I'm looking for. I guess I will work without shared cache for now and hope that

[sqlite] Busy Handler in Shared Cache Mode?

Hello, I have program that continuously runs several threads (about 4 to 10) which concurrently read and write to an SQLite database. From http://www.sqlite.org/sharedcache.html it seems to me that I should be using shared cache mode. Until now, I have set the busy timeout to 500 msec and never

Re: [sqlite] Reasons for SQLITE_BUSY

"Edzard Pasma" <edz...@volcanomail.com> writes: > --- nikol...@rath.org wrote: >> "Igor Tandetnik" <itandet...@mvps.org> writes: >>> Nikolaus Rath <nikol...@rath.org> wrote: >>>> I am accessing the same database from differe

Re: [sqlite] Reasons for SQLITE_BUSY

"Igor Tandetnik" <itandet...@mvps.org> writes: > Nikolaus Rath <nikol...@rath.org> wrote: >> I am accessing the same database from different threads. Each thread >> has its own connection. I have set the busy timeout for each >> connection to 5000 milli

[sqlite] Lock type for SAVEPOINT

Hello, When there is no transaction active and I issue a SAVEPOINT command, does that start an IMMEDIATE, EXCLUSIVE or DEFERRED transaction? And is there any way I can influence this? Thanks, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8

[sqlite] Reasons for SQLITE_BUSY

Hello, I am accessing the same database from different threads. Each thread has its own connection. I have set the busy timeout for each connection to 5000 milliseconds. However, in some testcases I still get SQLITE_BUSY errors from sqlite3_step. Moreover, the whole testcases run in much less

Re: [sqlite] Getting last inserted rowid?

John Machin <sjmac...@lexicon.net> writes: > On 6/06/2009 8:19 AM, Nikolaus Rath wrote: >> John Machin <sjmac...@lexicon.net> writes: >>>> Now I'm confused. I want to know if it will be sufficient to wrap my >>>> last_insert_rowid() call between BEG

Re: [sqlite] Getting last inserted rowid?

"Igor Tandetnik" <itandet...@mvps.org> writes: > Nikolaus Rath wrote: >> "Igor Tandetnik" <itandet...@mvps.org> writes: >>> Nikolaus Rath <nikol...@rath.org> wrote: >>>> How can I determine the rowid of the last insert if I am acc

Re: [sqlite] Getting last inserted rowid?

John Machin writes: >> Now I'm confused. I want to know if it will be sufficient to wrap my >> last_insert_rowid() call between BEGIN .. and END in order to make it >> return the rowid that was last inserted by the same thread even if >> multiple threads are using the same

Re: [sqlite] Getting last inserted rowid?

John Machin <sjmac...@lexicon.net> writes: > On 4/06/2009 8:22 AM, Nikolaus Rath wrote: >> Nuno Lucas <ntlu...@gmail.com> writes: >>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath <nikol...@rath.org> wrote: >>>> Nuno Lucas <ntlu...@gmail.com> w

Re: [sqlite] Getting last inserted rowid?

Nuno Lucas <ntlu...@gmail.com> writes: > On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath <nikol...@rath.org> wrote: >> Nuno Lucas <ntlu...@gmail.com> writes: >>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath <nikol...@rath.org> wrote: >>>> Hell

Re: [sqlite] Getting last inserted rowid?

Nuno Lucas <ntlu...@gmail.com> writes: > On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath <nikol...@rath.org> wrote: >> Hello, >> >> How can I determine the rowid of the last insert if I am accessing the >> db from different threads? If I understand corre

[sqlite] UPDATE while SELECT is active

Hello, I am pretty sure that at some point I have read if the following code might bring me into trouble: cur1 = conn.get_cursor() cur2 = conn.get_cursor() res = cur1.execute("SELECT id FROM data WHERE enabled == 1") for name in res: cur2.execute("UPDATE data SET enabled = 0 WHERE id=?",

Re: [sqlite] Getting last inserted rowid?

"Igor Tandetnik" <itandet...@mvps.org> writes: > Nikolaus Rath <nikol...@rath.org> wrote: >> How can I determine the rowid of the last insert if I am accessing the >> db from different threads? If I understand correctly, >> last_insert_rowid() won't wo

[sqlite] Getting last inserted rowid?

Hello, How can I determine the rowid of the last insert if I am accessing the db from different threads? If I understand correctly, last_insert_rowid() won't work reliably in this case. I can't believe that I really have to do a SELECT on the data that I just INSERTed only to get the rowid...

Re: [sqlite] Does changing the db change result sets?

"Igor Tandetnik" <[EMAIL PROTECTED]> writes: > "Nikolaus Rath" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> What happens if >> >> 1. I prepare and execute a select statement >> 2. I retrieve a couple of

[sqlite] Does changing the db change result sets?

Hello, What happens if 1. I prepare and execute a select statement 2. I retrieve a couple of rows 3. I execute a new query that would change the result of the query in 1 4. I continue to retrieve the results of 1) Will I get the results as if step 3 hasn't happened, is the result