[sqlite] Proper way to change temp directory
Out of curiosity, what's the proper way to change the temp directory (say, to avoid Error: database or disk full errors on vacuum, which I ran into)? temp_store_directory has been working for me but it's deprecated and may be elided from builds. Is the only option to recompile sqlite? Thanks. -- Yang Zhang http://yz.mit.edu/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proper way to change temp directory
Cool beans, perhaps this should be added to the docs! On Sun, Nov 20, 2011 at 1:36 AM, Dan Kennedy danielk1...@gmail.com wrote: On 11/20/2011 04:00 PM, Yang Zhang wrote: Out of curiosity, what's the proper way to change the temp directory (say, to avoid Error: database or disk full errors on vacuum, which I ran into)? temp_store_directory has been working for me but it's deprecated and may be elided from builds. Is the only option to recompile sqlite? Thanks. On unix setting the TMPDIR environment variable works. On windows SQLite uses GetTempPath(). So perhaps setting TMP or TEMP works there. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Yang Zhang http://yz.mit.edu/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this a bug? Can't tell from docs....
I just got bit by some peculiar behavior in sqlite where id int primary key is different from: id integer primary key In particular, sqlite will generate values for the latter but not the former: sqlite create table a (a integer primary key, b integer);sqlite insert into a (b) values (0);sqlite select * from a;1|0sqlite create table b (a int primary key, b integer);sqlite insert into b (b) values (0);sqlite select * from b;|0 I couldn't find in http://www.sqlite.org/autoinc.html orhttp://www.sqlite.org/datatype3.html any mention of this peculiardistinguishing behavior. Anyway, if this is intentional (as I'm guessing), I wouldn't have been able to tell from the docs - perhaps this would warrant special mention? Just thought I'd bring this to your attention. -- Yang Zhang http://yz.mit.edu/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transaction isolation
Hi, for some reason the following program will loop forever:
#!/usr/bin/env python
import sqlite3
conn = sqlite3.connect(':memory:')
conn.text_factory = bytes
conn.execute('CREATE TABLE shelf (key INTEGER NOT NULL, value INTEGER
NOT NULL)')
for i in xrange(3):
conn.execute('INSERT INTO shelf (key, value) VALUES (?,?)', (i, i))
conn.commit()
for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM
shelf ORDER BY ROWID')):
conn.execute('REPLACE INTO shelf (key, value) VALUES (?,?)', (i, i))
conn.commit()
print i
Anybody understand why? I thought the REPLACE and SELECT transactions
should be (snapshot) isolated from each other, so why does the SELECT
keep getting updated rows from the REPLACE? Any way to solve this
problem? So far all I've found are commands that can change the
connection-level isolation/locking, but not the cursor-level
(transaction-level) isolation.
Thanks in advance for any answers!
--
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
Roger Binns wrote:
Yang Zhang wrote:
for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM
shelf ORDER BY ROWID')):
You are converting the key which is an integer into a string for no
apparent reason.
I copied and pasted this code straight from my actual application, which
uses blobs instead of integers, which I need to convert into strings
(since Python interfaces with blobs using the `buffer` type, not `str`).
If you also ask for the ROWID you will see that what is happening is a
new rowid is generated for the replaced row so that if you are iterating
over the table while modifying it then you effectively have an infinite
length table.
This is unusual for a RDBMS that claims to provide ACID properties - in
particular, this is not even as strong an isolation level as snapshot
isolation, as a reader transaction is able to see a concurrent writer
transaction's effects. In fact, this is weaker than the weakest
isolation level in (say) Postgresql, which is READ COMMITTED (in which
any statement is guaranteed to not see the effects of a transaction that
is committed after the query has started execution).
Any way to solve this problem?
You currently have the SELECT results being read back one at a time
(lazily) on each iteration of the for loop. The simplest solution is to
read them all in first. Add .fetchall() after the execute.
Unfortunately in the original application the table is large (many GBs).
Any way to solve this problem without first reading everything into
memory, and without manually creating a second copy of the table? Is
there no way to request a stronger isolation level, such as snapshot
isolation?
--
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
John Elrick wrote:
Yang Zhang wrote:
Roger Binns wrote:
Yang Zhang wrote:
for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM
shelf ORDER BY ROWID')):
You are converting the key which is an integer into a string for no
apparent reason.
I copied and pasted this code straight from my actual application, which
uses blobs instead of integers, which I need to convert into strings
(since Python interfaces with blobs using the `buffer` type, not `str`).
If you also ask for the ROWID you will see that what is happening is a
new rowid is generated for the replaced row so that if you are iterating
over the table while modifying it then you effectively have an infinite
length table.
This is unusual for a RDBMS that claims to provide ACID properties - in
particular, this is not even as strong an isolation level as snapshot
isolation, as a reader transaction is able to see a concurrent writer
transaction's effects. In fact, this is weaker than the weakest
isolation level in (say) Postgresql, which is READ COMMITTED (in which
any statement is guaranteed to not see the effects of a transaction that
is committed after the query has started execution).
As I am not an expert in the Python wrapper, I could be incorrect;
however, your code as written appears to be equivalent to the following:
begin transaction
for select(
insert stuff
end
commit
rather than your intended:
s = select(...
begin transaction
for s...
insert stuff
end
commit
I say this because your example implies that the Python wrapper starts
the transaction automatically inside the execute, and I would not be
surprised if it did so BEFORE executing the SQL parameter.
The cursor() method that I call on the conn for the SELECT should give
me a separate transaction.
--
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
Yang Zhang wrote:
John Elrick wrote:
Yang Zhang wrote:
Roger Binns wrote:
Yang Zhang wrote:
for i in (str(row[0]) for row in conn.cursor().execute('SELECT key
FROM shelf ORDER BY ROWID')):
You are converting the key which is an integer into a string for no
apparent reason.
I copied and pasted this code straight from my actual application,
which uses blobs instead of integers, which I need to convert into
strings (since Python interfaces with blobs using the `buffer` type,
not `str`).
If you also ask for the ROWID you will see that what is happening is a
new rowid is generated for the replaced row so that if you are
iterating
over the table while modifying it then you effectively have an infinite
length table.
This is unusual for a RDBMS that claims to provide ACID properties -
in particular, this is not even as strong an isolation level as
snapshot isolation, as a reader transaction is able to see a
concurrent writer transaction's effects. In fact, this is weaker
than the weakest isolation level in (say) Postgresql, which is READ
COMMITTED (in which any statement is guaranteed to not see the
effects of a transaction that is committed after the query has
started execution).
As I am not an expert in the Python wrapper, I could be incorrect;
however, your code as written appears to be equivalent to the following:
begin transaction
for select(
insert stuff
end
commit
rather than your intended:
s = select(...
begin transaction
for s...
insert stuff
end
commit
I say this because your example implies that the Python wrapper starts
the transaction automatically inside the execute, and I would not be
surprised if it did so BEFORE executing the SQL parameter.
The cursor() method that I call on the conn for the SELECT should give
me a separate transaction.
I also tried using separate connections, but that just ends up blocking
and failing with a timeout on the lock acquisition because it appears
that SQLite only has full-table locking, and not MVCC/snapshot
isolation. Do I need to manually extract out all the data first into
another store, and then iterate over that to operate on original database?
--
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
Roger Binns wrote: Yang Zhang wrote: I copied and pasted this code straight from my actual application, which uses blobs instead of integers, which I need to convert into strings (since Python interfaces with blobs using the `buffer` type, not `str`). And for very good reason. Blobs are buckets of bytes and those are not strings. In your example there was no need to do the conversion since you can supply buffers as values too. (In Python 3 the bytes type is used.) Actually, this is only because Python 3 str is Python 2 unicode. Python 2 (which I'm currently using, and which I believe most of the world is using) str is a physical string of bytes, not a logical/decoded character string. Python 2.6 introduces bytes as a synonym for str, but I am using Python 2.5 at the moment. From http://mail.python.org/pipermail/python-list/2009-January/696449.html: In Python 2.x, str means string of bytes. This has been renamed bytes in Python 3. In Python 2.x, unicode means string of characters. This has been renamed str in Python 3. -- Yang Zhang http://www.mit.edu/~y_z/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
Pavel Ivanov wrote: BTW, ACID that you mentioned has nothing to do with snapshot isolation that you want to achieve. AFAIK only Oracle supports this kind of statement isolation level. Actually, Oracle, Postgresql, SQL Server, Firebird, and others support snapshot isolation. http://en.wikipedia.org/wiki/Snapshot_isolation And I certainly hope I did not convey that ACID implies snapshot isolation. -- Yang Zhang http://www.mit.edu/~y_z/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
Igor Tandetnik wrote: Yang Zhang yanghates...@gmail.com wrote: Pavel Ivanov wrote: BTW, ACID that you mentioned has nothing to do with snapshot isolation that you want to achieve. AFAIK only Oracle supports this kind of statement isolation level. Actually, Oracle, Postgresql, SQL Server, Firebird, and others support snapshot isolation. http://en.wikipedia.org/wiki/Snapshot_isolation ... but not between two statements running within _the same transaction_. Isolation level (snapshot or otherwise) describes how two transactions are isolated from each other. In your example, you only have one transaction, so any discussion of isolation levels is moot. Right, I mean the whole reason why I originally wrote to this list was because I was under the (incorrect) impression that I was working with two separate transactions. -- Yang Zhang http://www.mit.edu/~y_z/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction isolation
Roger Binns wrote: Yang Zhang wrote: Actually, this is only because Python 3 str is Python 2 unicode. Python 2 (which I'm currently using, and which I believe most of the world is using) str is a physical string of bytes, not a logical/decoded character string. Python 2.6 introduces bytes as a synonym for str, but I am using Python 2.5 at the moment. This is all pedantically true, but it is still a really bad way to structure your program? Did you read the Joel Unicode and character sets link? I *have* in fact read that article a very, very long time ago, but that is besides the point. I am aware of character encoding issues, thanks. It was because Python 2 messed up on bytes versus strings versus unicode that they had to clean it up in Python 3. It is also why the SQLite wrappers in Python 2 return blobs as the buffer type so that there is no accidental mingling of bytes and strings. (Disclosure: I am the author of the APSW wrapper) SQLite *only* supports Unicode strings. Other databases do support non-Unicode strings, character set conversions and all that other complexity. I require str because that is what cPickle.loads() requires; you cannot pass it a buffer. I need to store pickled objects in the database because I am implementing an SQLite backend for the Python 2 shelve module. -- Yang Zhang http://www.mit.edu/~y_z/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
