I'm having a problem with a create … from … order by when my data starts
approaching 2GB. I'm using SQLite 3.7.14 on FreeBSD 8.3-RELEASE-p3.
The processing starts with an unsorted table, created thus:
CREATE TABLE all_headings (key, heading);
Then it creates a sorted version of the table:
create table headings as select * from all_headings order by key;
This is fine on small data, but when I load 1.8GB of data (8.8 million rows)
the second CREATE fails, reporting a disk I/O error. If I remove the "order by"
clause, the create succeeds. (SQLite was compiled with large file support, and
I could create a 4GB database using .import so it's not a file system
limitation, and the /tmp space is plenty large.)
[At that point it looks like pre-sorting the data before loading has some
appeal, but the code maintainer prefers to treat SQLite as the authority on
sorting rather to mess with the many versions of sort(1) on the various UNIXes
and Windows. I understand his point.]
So trying to understand the error with the ORDER BY clause, I loaded up the
unsorted all_headings table and then trussed sqlite3 running the CREATE
TABLE…ORDER BY. Before the error, there's a lot of lseek()/read() of the .db
file, and a lot of lseek()/write() to temp file (in /tmp, I assume this is the
sort space). Then there's a read() of the temp file, which returns an error.
From truss:
read(5,0x800f64108,-1834983915) ERR#22 'Invalid argument'
man 2 read says read's type signature is:
ssize_t read(int d, void *buf, size_t nbytes);
and it says this about read returning errno 22:
[EINVAL] The pointer associated with d was negative.
[EINVAL] The value nbytes is greater than INT_MAX.
The pointer doesn't look negative, but that nbytes argument looks possibly a
problem. size_t is 64-bit on this system, but INT_MAX = 2147483647, or the max
for a 32-bit signed int. Though truss doesn't know signed from unsigned valued,
the size_t nbytes value that truss reports is greater than MAX_INT. So I think
that explains the error.
The main question is: is there anything to be done to get that CREATE TABLE …
ORDER BY to work? Based on the truss output, I suspect not, but maybe someone
here has run into the problem before.
Thanks,
-Tod
Tod Olson <t...@uchicago.edu>
Systems Librarian
University of Chicago Library
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
