Can I fetch the result of a SELECT query iteratively using LIMIT and OFFSET?

Question

I am implementing a FUSE file system that uses a sqlite3 database for its backend. I do not plan to ever change the database backend as my file system uses sqlite3 as a file format. One of the functions a file system must implement is readdir function. This function allows the process to iteratively read the contents of a directory by repeatedly calling it and getting the next few directory entries (as many as the buffer can hold). The directory entries are returned may be returned in any order. I want to implement this operation with the following query:

SELECT fileno, name FROM dirents WHERE dirno = ? LIMIT -1 OFFSET ?;

where dirno is the directory I'm reading from and OFFSET ? is the number of entries I've already returned. I want to read as many rows as I can fit into the buffer (I cannot predict the count as these are variable-length records depending on the length of the file name) and then reset the query.

Due to the stateless nature of FUSE, keeping open a query and returning the next few rows until the directory has ended is not an option as I cannot reliably detect if the process closes the directory prematurely.

The dirents table has the following schema:

CREATE TABLE dirents (
    dirno INTEGER NOT NULL REFERENCES inodes(inum),
    fileno INTEGER NOT NULL REFERENCES inodes(inum),
    name TEXT NOT NULL,
    PRIMARY KEY (dirno, name)
) WITHOUT ROWID;

Question

In theory, a SELECT statement yields rows in no defined order. In practice, can I assume that when I execute the same prepared SELECT statement multiple times with successively larger OFFSET values, I get the same results as if I read all the data in a single query, i.e. the row order is the same unspecified order each time? An assumption that currently holds is that the database is not modified between queries.

Can I assume that the row order stays reasonably similar when a different query modifies the dirents table inbetween? Some glitches (e.g. directory entries appearing twice) will of course be observable by the program, but for usability (the main user of readdir is the ls command) it is highly useful if the directory listing is usually mostly correct.

If I cannot make these assumptions, what is a better way to reach the desired result?

I know that I could throw in an ORDER BY clause to make the row order well-defined, but I fear that this might have a strong impact on performance, especially when reading small chunks of a large directory—the directory has to be sorted every time a chunk is read.

Gordon Linoff · Accepted Answer

The right solution to this problem is to use order by. If you are concerned about the performance of the order by, then use an index on the column used for the order by.

The simplest approach, in my opinion, would be to remove the without rowid option on the table creation. Then you can just access the table as:

SELECT fileno, name
FROM dirents
WHERE dirno = ?
ORDER BY rowid
LIMIT -1 OFFSET ?;

I realize this adds additional bytes to each row, but it is for a good purpose -- making sure that your queries are correct.

Actually, the best index for this table would be on dirno, rowid, fileno, name. Given the where clause, you are doing a full table scan anyway, unless you have an index.

Can I fetch the result of a SELECT query iteratively using LIMIT and OFFSET?

Question

Answers (2)

Related Questions