Re: [sqlite] vacuum and rowids
On Apr 28, 2011, at 12:41 PM, Pavel Ivanov wrote: >> After more poking, it appears that rowids might not be changed by a vacuum >> if I have an index on the table. Is this true? If so, is it something I can >> rely on going forward? > > No, it's not true. The only way to keep your rowids intact is to declare an > INTEGER PRIMARY KEY alias for it. And you better never reference "rowid" name > in your application or your database schema. Can you explain this in more detail? I've never seen any prohibition on using "rowid" in the SQLite docs before. The page on autoincrement says "You can access the ROWID of an SQLite table using one the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID." which suggests that referring to rowids is fine. If I add a "rowid integer primary key" column on my tables, it seems like everything would work the way I want it to with minimal code changes. Any reason that won't work? -D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] vacuum and rowids
When the VACUUM feature was added I took a look at using it to keep database file sizes down, but discovered that it changed rowids and messed up my references between tables (or what I gather the database people call "foreign keys"). I'm playing around with this again and it looks like rowids aren't affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild the existing tables if I don't have to. After more poking, it appears that rowids might not be changed by a vacuum if I have an index on the table. Is this true? If so, is it something I can rely on going forward? Thanks! -Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] llvm/ppc compile bug
Just a note to share a problem I ran into recently: Compiling sqlite 3.6.22 with -arch ppc -Os on the llvm that ships with Xcode 3.2.1, the sqlite3AtoF function appears to have an infinite loop. If you compile the sqlite3 command line tool in this way, just executing "select round(1234);" will cause it to hang. This version of llvm reports itself as i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5646) (LLVM build 2206) I haven't checked to see if there's a newer llvm which fixes this. Apple dudes: this is bugreporter #7599241. Hope this saves someone out there some trouble. :) -D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re: Thread safety guarantees
On Sep 4, 2005, at 5:53 AM, Igor Tandetnik wrote: Christopher R. Palmer wrote: Unfortunately, that's not the case. The code that was being used when I created ticket 1272 was very simple. In the main thread, I opened a handle for each thread. Each thread then worked independently using only their own handle. This broke the locking because the low-level Linux file locks are tied to the thread that created them. That is, the thread that opened the handle.. So SQLite locks the DB file immediately after opening a connection in sqlite_open? If all activity on the connection must be restricted to a single thread, how is one to properly use sqlite3_interrupt? Hi Igor, I hope someone will correct me if I'm wrong, but it looks like calling sqlite3_interrupt from another thread is okay-ish. It only checks the sqlite3 struct's magic value to make sure it's legitimate (closed, open, or busy), and then sets the SQLITE_Interrupt bit on the flags field. I don't know if bit set and clear are atomic or not; if not, and two threads happen to write into the flags field at the same time, bad things can happen. I just fixed my code to work with the new thread restrictions and haven't had any problems calling sqlite3_interrupt from the UI thread so far. -D
Re: [sqlite] Crashing on some computers
On Nov 8, 2004, at 10:51 AM, Clay Dowling wrote: Tomas Franzén said: On 2004-11-09, at 16.42, b.bum wrote: Are you statically linking SQLite or using a dylib? I don't know. That's not a good sign, is it? ;-) I have compiled SQLite and added libsqlite.o and sqlite.h to the project items in XCode. ldd is your friend here. It will show you if you have an external dependency on any libraries, not just SQLite. On OS X, there's no ldd: 'otool -L' does the same thing, though. Wish they'd provided an alias for the six months it took me to learn that. :) -D
Re: [sqlite] How Disable Journaling on Windows
On Oct 12, 2004, at 12:33 PM, Shawn Walker wrote: How do I disable Journalling in SQLite 3.0? I thought setting "PRAGMA temp_store=MEMORY;" would do that? Try: PRAGMA synchronous = OFF; http://www.sqlite.org/lang.html#pragma_synchronous Did I read somewhere that default_synchronous is no longer used, or am I having memory hallucinations again? -D
Re: [sqlite] Deadlock when doing threaded updates and inserts
On Aug 11, 2004, at 4:05 PM, tezozomoc wrote: I have solved this problem by writing a wrappers around sql_exec and sql_query, sql_step, etc... In these wrappers I handle the waiting for busy and the lock file issue... I was doing the same, calling usleep() whenever I got a SQLITE_BUSY return and trying the command again, but it doesn't help in the case where two threads are both in a transaction and trying to write.. -D
Re: [sqlite] Deadlock when doing threaded updates and inserts
On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote: Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed. Wow. That adds a whole lot of complexity to my code. Every transaction would be inside a loop that checks for a busy return from any statement within. And most of the places I'm using a transaction, I'm doing a few hundred inserts or updates from a number of different functions. This really is something I'd expect to run under the hood. Since only one of the competing threads will have completed a write (right?), can't the others "postpone" their transactions somehow until they can get a write lock? For now, I've solved the problem by adding my own locks to exclude simultaneous transactions on the same database file. I'm only using transactions for writes (is there any reason for a read-only transaction?) so if there's no way to resolve two opened write transactions, you shouldn't be able to open two in the first place. Please let me know if there's something I'm missing here.. Thanks, -Dave
[sqlite] Deadlock when doing threaded updates and inserts
I'm running into a deadlock, as the subject says, when doing updates on
a table in one thread while another thread is inserting into the same
table. (Oh, and this is on 3.0.4, compiled with --enable-threadsafe)
The update thread returns from its UPDATE command (within a
transaction) with SQLITE_BUSY when it sees a pending lock. The insert
thread returns SQLITE_BUSY from END TRANSACTION when it can't get an
exclusive lock.
Attached is a simple C program that demonstrates this. I open two
database handles on the same file (with a table "test" with a single
column "num") and do:
db1: BEGIN TRANSACTION;
db2: BEGIN TRANSACTION;
db1: INSERT INTO test VALUES ( 1 );
At this point, both of these return SQLITE_BUSY:
db2: UPDATE test SET num = 2 WHERE num = 1;
db1: END TRANSACTION;
Is this a bug? Or do I have to do something with sqlite 3 I didn't with
2?
Thanks,
-Dave
#include
#include
#include
#include "sqlite3.h"
int execQuery(sqlite3* db, char* query)
{
char* err;
int rc = sqlite3_exec(db, query, NULL, NULL, &err);
if ( rc != SQLITE_OK )
{
printf("sqlite3_exec error: %s\n", err);
sqlite3_free(err);
return 0;
}
return 1;
}
int main()
{
sqlite3* db1;
sqlite3* db2;
int rc;
unlink("./test.db");
unlink("./test.db-journal");
rc = sqlite3_open("test.db", &db1);
if ( rc != SQLITE_OK )
{
printf("thread: Couldn't open database1\n");
exit(-1);
}
execQuery(db1, "CREATE TABLE test ( num int );");
rc = sqlite3_open("test.db", &db2);
if ( rc != SQLITE_OK )
{
printf("thread: Couldn't open database2\n");
exit(-1);
}
execQuery(db1, "BEGIN TRANSACTION;");
execQuery(db2, "BEGIN TRANSACTION;");
execQuery(db1, "INSERT INTO test VALUES ( 1 );");
while ( !execQuery(db2, "UPDATE test SET num = 2 WHERE num = 1;") &&
!execQuery(db2, "END TRANSACTION;") &&
!execQuery(db1, "END TRANSACTION;") )
usleep(1000);
execQuery(db2, "END TRANSACTION;");
return 0;
}
Re: [sqlite] sqlite3 crashing on OS X
On Jul 26, 2004, at 2:13 PM, Gus Mueller wrote: What's interesting is that balance_nonroot doesn't call balance_shallower- it calls balance, and then balance calls balance_shallower. Yeah, it looks like a frame header is getting tromped on and confusing the debugger. I filed it at bugreporter.apple.com--we'll see if they can be convinced it's their fault. (If there's any CoreData lurkers out there, it's radar #3741210..) More evidence it's Apple's problem: it works fine when you use pthread_create() instead of [NSThread detachNewThreadSelector:::] (thanks to Michael Robinette for finding this out). Odd, since NSThread uses pthreads itself. -D
Re: [sqlite] like, but not equal?
On Jul 24, 2004, at 1:32 AM, [EMAIL PROTECTED] wrote: sqlite> select count(*) from newsgroups where name = 'rec.arts.anime.fandom'; 0 sqlite> select count(*) from newsgroups where name like 'rec.arts.anime.fandom'; 1 Figured it out: I was using sqlite3_bind_blob(), but if I change to sqlite3_bind_text() it works right. So I'm guessing that a string on the command line is a text value and can never be strictly equal to a blob value (even if they're byte-for-byte the same), but LIKE coerces the blob into text? -D
Re: [sqlite] FW: 2.8.14 performance, Indexed Table INSERTS
On Jun 20, 2004, at 9:07 PM, Darren Duncan wrote: Generally speaking, you should only use indexes on table columns that have a lot of distinct values, and each one only appears a few times. You should not use indexes on columns that have few distinct values and each appears many times; in the latter case, a full table scan would be faster. That's weird. I would have thought that having any index at all to pare down the result set would make things faster..? Wouldn't the select here: CREATE TABLE tmp ( flag boolean, name text ); SELECT name FROM tmp WHERE flag = 1 AND name LIKE '%foo%'; run faster with an index on the flag column since it can scan just the flag = 1 rows instead of the full table? -Dave - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] more performance questions
On Mar 18, 2004, at 1:30 PM, Corporate wrote: Two words! Thread Synchronization! (above the database API in your code). Works like a charm. I just sat down with a cup of coffee and came to the came conclusion--if I keep only one connection to the database and do the mutexing myself, I get better control and I get to use temp tables, too! I think I'm going to queue up data as it comes in, then flush it to the database 5000 or so rows at a time. That should be fast enough to keep the UI from hanging and big enough to get a boost from the transaction. -D - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] more performance questions
I'm inserting a bunch of data loaded off the network into a table. Here at the office, SQLite keeps up pretty well; at home on the cable modem, it's a huge bottleneck. Loading now takes about 10x what it used to when we were just storing in memory. Yes, I'm doing BEGIN/END around the entire transaction. I've removed indexes and set PRAGMA default_synchronous = OFF on the database, but it didn't have much effect. Oh, and I'm using a pre-compiled query to do the insert, too. I can't use a temporary table, either, because I need to share the data between the network and UI threads. The SQLite optimization FAQ at: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html mentions turning off journaling as a last resort. I tried this by forcing 'omitJournal = 1;' at the beginning of sqliteBtreeFactory(), but it causes problems on down the line (failed assertion on pPager->journalOpen in sqlitepager_commit()). Is there another way to do this? I'd like to see if we'll even be able to get the SQLite overhead low enough to use it or if I have to start over. This isn't a high-reliability context--if the database gets corrupted, we can just toss it and load back off the network. Any thoughts? -D - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] multithreading
After reading that SQLite should Just Work, I dug through my code and found my bug: I was calling sqlite_reset before invoking my precompiled queries instead of afterwards. I changed it around and it seems to be fixed--leaving the vm in a running state must have held a lock somewhere? Many thanks for the help! -D - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
