[sqlite] whish list for 2016
I left the list some days and find this subject discussed and finished!! Happy New Year 2016 to every colister and sqlite developer. I add my list and a tiny explanation. - Lock tables/indexes on cache (make them unevictable) When a query use a big size table, other tables/index used in the query or other queries tables/indexes are dropped from sqlite cache. Lock content on cache could boost those queries that "fights" with a big size table. Mark a table as non cacheable could do the same in my use case. As a working example I c from own code, a sqlite filesystem that stores file content as blob or as external file: CREATE TABLE `file` ( \ `fid` INTEGER, \ `size` INTEGER NOT NULL, \ `permission`INTEGER NOT NULL DEFAULT 700, \ `lock` INTEGER NOT NULL DEFAULT 0,\ `blob_id` INTEGER NOT NULL, \ `owner_id` INTEGER NOT NULL, \ `group_id` INTEGER NOT NULL, \ `name` TEXT NOT NULL, \ `external_path` TEXT NOT NULL, \ PRIMARY KEY(fid) \ ); CREATE TABLE `fsblob` (\ `bid` INTEGER, \ `size` INTEGER NOT NULL, \ `fid` INTEGER NOT NULL, \ `compressed`INTEGER NOT NULL DEFAULT 0,\ `content` BLOB, \ PRIMARY KEY(bid) \ ); \ \ Each time a user access a file, the "file" table (and others like "directory", "users") is evicted from cache (fsblob table fills cache) and needs to be reread and reparsed from disk for the next query. The problem is worse when multiple files are accessed. Making the cache bigger works upto some filesize, but locking the "important" tables on cache is the correct (I think) fix. As a workaround, I used multiple databases, one with fs metadata and attach others with file content (fsblob tables), but dropped it because other tecnical problems. - Access table row by fileposition In a read only scenario (no writer and/or no wal mode), read and parse the row content if we know it's absolute database file position or we know page number plus page position offset. - Lock constant tables on file (stick table rows to page and/or fileposition) This way, the file position of a row is fixed or easily calculated and can create/use hash indexes. Now I'm using a virtual table derived from Dr. Hipps csv virtual table years ago that creates a inmemory hash index of an external csv file, but it implies use of own sqlite3 version with virtual code and an additional file to database. Additionally, if we can lock constant tables (defined as is by the developer) at the begining of the database file, mmap feature can cache constant/static data from those tables. In my app, it implies file, user, group, fs and other tiny tables (changes are rare) could be mmaped. - Vacuum/backup reorder tables in database file If we can tell vacuum or backup the order we want the tables be on the database file, on some scenarios (like mine;) ) moving tables to the begining (constant data) or the end (timestamp log growing table, f.ex.) could speed up queries - Vacuum/backup adds free pages for table/index grow As far as I know, vacuum and backup write database tables, indexes and metadata without free pages. Adding free pages at the end of the pages that store a table or index, and reserve those pages for use of that table or index could hold back internal fragmentation. There are others ideas but... hey 2017 is closer ;) Thanks to Dr. Richard Hipps, Dan Kennedy and Joe Mistachkin for their exceptional work, and other colisters for their time and help. --- --- Eduardo Morras
[sqlite] Apparent sqlite bug
> On 3 Jan 2016, at 6:52pm, Richard Hipp wrote: > > On 1/3/16, Simon Slavin wrote: >> >> I've seen references to imposter tables in the SQLite comments. What is an >> imposter table ? > > An undocumented and unsupported feature that allows two or tables in > the schema to refer to the same b-tree. This is used by the RBU > extension in order to update indexes and table independently of one > another. Thanks very much. I liked the idea suggested by the name. I can see how that would be useful. Simon.
[sqlite] Apparent sqlite bug
On 03-01-16 00:11, richard parkins wrote:
> An INSERT statement which fails with no explicit conflict clause appears to
> throw away a pending SAVEPOINT.
> The following sequence demonstrates this behaviour
> SAVEPOINT demonstration;
> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
> "last name" "TEXT",
> "address",
> PRIMARY KEY ( "first name", "last name" )
> ) WITHOUT ROWID;
> INSERT INTO "PK" default values;
> ROLLBACK TO demonstration;
>
> As expected, the insert fails with Error: NOT NULL constraint, but the
> ROLLBACK statement then also fails with Error: no such savepoint.
> If INSERT is replaced by INSERT OR ABORT (which is supposed to be the
> default), the ROLLBACK statement does not fail.
> Environment:- sqlite 3.9.2 built from the amalgamation, running on UBUNTU
> Linux 14.04.3 LTS. SQL statements run with sqliteman built from
> https://github.com/rparkins999/sqliteman.git.
>
> I originally saw this problem running the INSERT inside sqliiteman. If you
> run sqliteman, create the PK table as shown but omitting the WITHOUT ROWID
> clause, insert a row of all nulls, and then try using sqliteman's Alter Table
> function (accessible by right click on the table name) and try to change it
> to a WITHOUT ROWID table, you'll see no such savepoint: ALTER_TABLE Unable to
> fetch row. This is caused by sqliteman's internal cleanup algorithm trying to
> roll back after failing to insert the data from the old table into its newly
> created WITHOUT ROWID table, and not being able to do so because the
> savepoint has vanished. Changing INSERT in my code to INSERT OR ABORT makes
> the rollback work properly.
> Richard Parkins
>
a ROLLBACK was done because of the Error...
compare your code with this example:
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
...> "last name" "TEXT",
...> "address",
...> PRIMARY KEY ( "first name", "last name" )
...> ) WITHOUT ROWID;
sqlite> SAVEPOINT demonstration;
sqlite> INSERT INTO "PK" values ("1","1","1");
sqlite> SELECT * FROM "PK";
1|1|1
sqlite> INSERT INTO "PK" default values;
Error: NOT NULL constraint failed: PK.first name
sqlite> SELECT * FROM "PK";
sqlite>
sqlite>
[sqlite] Apparent sqlite bug
On 3 Jan 2016, at 5:49pm, Richard Hipp wrote: > /* Make sure every column of the PRIMARY KEY is NOT NULL. (Except, > ** do not enforce this for imposter tables.) */ I've seen references to imposter tables in the SQLite comments. What is an imposter table ? Simon.
[sqlite] Apparent sqlite bug
On 1/3/16, Simon Slavin wrote: > > On 3 Jan 2016, at 5:49pm, Richard Hipp wrote: > >> /* Make sure every column of the PRIMARY KEY is NOT NULL. (Except, >> ** do not enforce this for imposter tables.) */ > > I've seen references to imposter tables in the SQLite comments. What is an > imposter table ? > An undocumented and unsupported feature that allows two or tables in the schema to refer to the same b-tree. This is used by the RBU extension in order to update indexes and table independently of one another. -- D. Richard Hipp drh at sqlite.org
[sqlite] Apparent sqlite bug
On 1/3/16, Luuk wrote:
>
>
> On 03-01-16 00:11, richard parkins wrote:
>> An INSERT statement which fails with no explicit conflict clause appears
>> to throw away a pending SAVEPOINT.
>> The following sequence demonstrates this behaviour
>> SAVEPOINT demonstration;
>> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
>> "last name" "TEXT",
>> "address",
>> PRIMARY KEY ( "first name", "last name" )
>> ) WITHOUT ROWID;
>> INSERT INTO "PK" default values;
>> ROLLBACK TO demonstration;
>>
>> As expected, the insert fails with Error: NOT NULL constraint, but the
>> ROLLBACK statement then also fails with Error: no such savepoint.
>
> a ROLLBACK was done because of the Error...
>
Yeah, but a constraint failure should only terminate the one statement
that encountered the problem and should *not* rollback the
transaction.
The problem is that the NOT NULL constraint on the PRIMARY KEY of a
WITHOUT ROWID table was defaulting to give ON CONFLICT ROLLBACK
behavior instead of ON CONFLICT ABORT, as it should. I'm currently
testing the following fix:
Index: src/build.c
==
--- src/build.c
+++ src/build.c
@@ -1772,11 +1772,11 @@
/* Make sure every column of the PRIMARY KEY is NOT NULL. (Except,
** do not enforce this for imposter tables.) */
if( !db->init.imposterTable ){
for(i=0; iaCol[pPk->aiColumn[i]].notNull = 1;
+ pTab->aCol[pPk->aiColumn[i]].notNull = OE_Abort;
}
pPk->uniqNotNull = 1;
}
/* The root page of the PRIMARY KEY is the table root page */
Assuming the patch above works, I'll check in the fix in a few minutes...
--
D. Richard Hipp
drh at sqlite.org
