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...
T
"Igor Tandetnik" writes:
> Nikolaus Rath 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 work reliably in this case.
>
> Last inser
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=?", nam
Nuno Lucas writes:
> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote:
>> 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 rel
Nuno Lucas writes:
> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote:
>> Nuno Lucas writes:
>>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote:
>>>> Hello,
>>>>
>>>> How can I determine the rowid of the last insert if I am accessi
John Machin writes:
> On 4/06/2009 8:22 AM, Nikolaus Rath wrote:
>> Nuno Lucas writes:
>>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote:
>>>> Nuno Lucas writes:
>>>>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote:
>>>>>
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 connection (but differen
"Igor Tandetnik" writes:
> Nikolaus Rath wrote:
>> "Igor Tandetnik" writes:
>>> Nikolaus Rath wrote:
>>>> How can I determine the rowid of the last insert if I am accessing
>>>> the db from different threads? If I understand correc
John Machin writes:
> On 6/06/2009 8:19 AM, Nikolaus Rath wrote:
>> 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
>>>> r
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 tha
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
"Igor Tandetnik" writes:
> Nikolaus Rath 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 milliseconds.
>>
>> However, in some te
"Edzard Pasma" writes:
> --- nikol...@rath.org wrote:
>> "Igor Tandetnik" writes:
>>> Nikolaus Rath wrote:
>>>> I am accessing the same database from different threads. Each
>>>> thread
>>>> has its own connection.
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 h
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 unlock_notify becomes stable
a
t; 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 wrote:
>> Hello,
>>
>> I have program that continuously run
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 1663
"Igor Tandetnik" 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,?), ctime=?, mtime=?
>> WHERE
Roger Binns 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 file locks. If
> you have conc
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
undefi
"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 rows
&
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 to
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 "
"D. Richard Hipp" 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
>> is no enclosing transa
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 s
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 above 2^32 while my
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, ho
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 database connection with
>> a ne
Edzard Pasma writes:
> Op 10-jan-2010, om 19:25 heeft Nikolaus Rath het volgende geschreven:
>
>> Filip Navara writes:
>>>> I am accessing the same database from several threads, each using a
>>>> separate connection. Shared cache is not enabled.
>>>
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 strace (see Roger Bin
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 dbdimp.c
>> line 423 [for Statement "S
"D. Richard Hipp" writes:
> On Jan 12, 2010, at 6:58 AM, Ian Jackson wrote:
>
>> Nikolaus Rath writes ("Re: [sqlite] Reasons for SQLITE_CANTOPEN"):
>>> Edzard Pasma writes:
>>>> Hope strace (see Roger Binns' post) will help you furthe
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 com
Roger Binns 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 than the "stripped" compress
Roger Binns 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(), because the following just p
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 3
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 variables are not volatile). Is
Roger Binns 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.
>
> We c
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 pthreadMutexEnter
>
> I
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
Alexey Pechnikov 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 fingerprint: 5B93 61F8 4EA2 E279 ABF6 02C
Simon Slavin 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 without disclosing potentially sen
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 what got me. Not only is
"Jay A. Kreibich" 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 get) a copy of
>> t
Roger Binns 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 the particular s
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. Did you know it doesn't i
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
2
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
EXCLUSIV
Dan Kennedy 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'
>>
>> What I think should be happening instead is
"Igor Tandetnik" writes:
> Nikolaus Rath wrote:
>> 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 a
Dan Kennedy 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'
>>
>> What I think should be happening instead is
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);
> sqlite> alter table foo add column b text(5) NO
Nikolaus Rath writes:
> Dan Kennedy 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'
&g
Nikolaus Rath 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
>SHARED lock if one executes a UPDATE query
Hi,
I only saw http://article.gmane.org/gmane.comp.db.sqlite.general/58835,
was there anything else?
-Nikolaus
Gerry Snyder writes:
> Er, did you not see Dan Kennedy's comments a fed days ago??
>
> On 8/24/10, Nikolaus Rath wrote:
>> Nikolaus Rath
>> writes
Doug Currie writes:
> On Aug 24, 2010, at 10:57 AM, Nikolaus Rath wrote:
>
>> Nikolaus Rath
>>
>> writes:
>>> Still no one able to clarify the issues raised in this thread?
>>>
>>> Let me try to summarize what I still don't understand:
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. For some reason while doing first delete SQLite actuall
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
--
»Time
"Igor Tandetnik" writes:
> Nikolaus Rath wrote:
>> 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":
&
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),
Igor Tandetnik 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 journal_mode = off and without wrapping the IN
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?
>
> WAL-journal is not some kind of change log with commit mark
Simon Slavin 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
>> small but the insertions take very long time. With a tr
Nikolaus Rath 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, creating the db takes 54 seconds.
&g
Nikolaus Rath 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 db is 24 MB big.
On
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 simi
Richard Hipp writes:
> On Tue, May 17, 2011 at 5:11 PM, Nikolaus Rath
> wrote:
>> 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
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 transa
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)')
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 block_id
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 inse
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
"Igor Tandetnik" writes:
> Nikolaus Rath wrote:
>> 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_aut
73 matches
Mail list logo