[sqlite] incorrect R-tree documentation

[Dan Kennedy]

On 10/03/2015 02:04 AM, Clemens Ladisch wrote:
> Hi,
>
> the R-tree documentation says:
> | Attempts to insert something other than an integer into the first
> | column, or something other than a numeric value into the other
> | columns, will result in an error.
>
> This is not actually true:
>
>> create virtual table t using rtree(id, x1, x2);
>> insert into t values(null, null, null);
>> insert into t values('xxx', 'xxx', x'1234');
>> select * from t;
> 1|0.0|0.0
> 0|0.0|0.0

Thanks for pointing this out. Docs now updated here:

   http://sqlite.org/docsrc/info/56eab0136ce41732

Dan.

[sqlite] (no subject)

[Dan Kennedy]

On 10/03/2015 03:19 AM, Andrew Cunningham wrote:
>
>
> SQLite uses a counter to enforce deferred foreign keys. Each time an
> operation violates a constraint it increments that counter.
>
> If the counter is greater than 0, then for every operation that might
> potentially correct the violation (which means virtually every
> operation
> on any table that is the parent or child in an FK constraint) it does
> extra seeks to see if it really does. i.e. each time you insert
> into the
> parent table, it seeks within the child table to see if the insert
> fixes
> a foreign key violation. And if it does, the counter is decremented.
>
> So we know there is overhead, but I'm quite surprised that it is 1000x
> slower.
>
> How large is the database? So large that these extra seeks could be
> performing real IO (i.e. having to go all the way to the disk for
> every
> seek, not just finding the data in the OS cache)?
>
>
> OK, that kinds of makes sense in explaining where the problem comes 
> from. I really do see an exponential growth in execution time 
> depending on how many INSERTS are made after the first FK violation 
> occurs.
>
> I have a very simple example that , for example, takes 0.2s with no FK 
> violations and 25s ( about  100x slower) when a single FK violation is 
> forced before I start doing the "mass creation".
>
> This is a small database, creating 500  parent objects, each with 50 
> child objects.
> If I change (double) the number of objects to 1000 the time changes to 
> 0.4s and 100s. It looks like an N^2 problem. So you can see that very 
> quickly my factor of 1000x is very easy to reach..
>
>
> When FK checking is in DEFERRED mode, the implication is that no FK 
> checking is made until the COMMIT.
>
> That's very useful, as often it is impossible to make the 
> modifications done in the correct order to satisfy all the 
> FK=IMMEDIATE constraints, even though the end result is a 
> "referentially correct" database.
>
> So what you are saying above makes DEFERRED mode a lot less useful.
>
> DISCLAIMER: I am use ODB ( the awesome C++/ORM tool) to do these tests.
>
>
>


Hi,

Ok. My first guess is that you are missing the "suggested index" on the 
child table. Without this, if there is a foreign key violation in the 
db, each insert on the parent table will cause a linear scan of the 
entire child table. With the index, it's just a simple seek.

https://www.sqlite.org/foreignkeys.html#fk_indexes

If you're unsure, open your database using the command line tool and 
dump the schema using the ".schema" command. Post that here and someone 
should be able to tell you if you're missing an index or not.

Dan.

[sqlite] When was PRAGMA busy_timeout added to SQLite3

[Simon Slavin]

On 3 Oct 2015, at 12:19am, Jim Morris  wrote:

> I'm using an archaic version, I know, but busy_timeout doesn't seem to work.  
> It doesn't return the value I set.  When was it added?
> 
> sqlite3 temp.db
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> PRAGMA busy_timeout=3;
> sqlite> PRAGMA busy_timeout;
> sqlite>

Don't know when, but it seems to have been fixed since the version you had:

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> PRAGMA busy_timeout;
0
sqlite> PRAGMA busy_timeout=3;
3
sqlite> PRAGMA busy_timeout;
3
sqlite> 

Simon.

[sqlite] (no subject)

[R.Smith]

On 2015-10-03 01:51 PM, Dan Kennedy wrote:
>
> Ok. My first guess is that you are missing the "suggested index" on 
> the child table. Without this, if there is a foreign key violation in 
> the db, each insert on the parent table will cause a linear scan of 
> the entire child table. With the index, it's just a simple seek. //...

This is quite probably the issue. It is extremely unlikely that anyone 
could have a reason for not having an index on a relation column, but it 
happens.

On this note, some other engines (such as MySQL) forces an Index to 
exist on a foreign key reference, or rather, it won't form a foreign key 
relation on a non-indexed column. I realize this is perhaps late in the 
life-cycle for SQLite3 to consider, but maybe SQLite4 could have similar 
enforcement?

[sqlite] When was PRAGMA busy_timeout added to SQLite3

[Richard Hipp]

On 10/2/15, Jim Morris  wrote:
> I'm using an archaic version, I know, but busy_timeout doesn't seem to
> work.  It doesn't return the value I set.  When was it added?

2012-12-12 (3.7.15)

>
> sqlite3 temp.db
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> PRAGMA busy_timeout=3;
> sqlite> PRAGMA busy_timeout;
> sqlite>
>
>


-- 
D. Richard Hipp
drh at sqlite.org