Documentation Source Text

Check-in [a8afb2f905]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Document the fact that the R-Tree extension is not capable of doing simultaneous reads and writes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a8afb2f9056eb408215c15c24659cfff0b0fae0988118d03f9288f04c5e68c49
User & Date: drh 2019-12-24 02:33:45.230
Context
2019-12-26
01:22
Push back the proposed release date for 3.31.0 by a month. (check-in: 157fdd299b user: drh tags: trunk)
2019-12-24
02:33
Document the fact that the R-Tree extension is not capable of doing simultaneous reads and writes. (check-in: a8afb2f905 user: drh tags: trunk)
2019-12-21
17:23
Enhancements and updates to the discussion of fuzzing in the testing document. (check-in: 3bf90e0caa user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/rtree.in.
255
256
257
258
259
260
261































































262
263
264
265
266
267
268

<p>However, for a "contained-within" style query, rounding the bounding
boxes outward might cause some entries to be excluded from the result set
if the edge of the entry bounding box corresponds to the edge of the query
bounding box.  To guard against this, applications should expand their
contained-within query boxes slightly (by 0.000012%) by rounding down the
lower coordinates and rounding up the top coordinates, in each dimension.
































































<h1>Using R*Trees Effectively</h1>

<p>
For SQLite versions prior to 3.24.0 ([dateof:3.24.0]),
the only information that an R*Tree index stores about an object is
its integer ID and its bounding box.  Additional information needs to







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331

<p>However, for a "contained-within" style query, rounding the bounding
boxes outward might cause some entries to be excluded from the result set
if the edge of the entry bounding box corresponds to the edge of the query
bounding box.  To guard against this, applications should expand their
contained-within query boxes slightly (by 0.000012%) by rounding down the
lower coordinates and rounding up the top coordinates, in each dimension.

<tcl>hd_fragment readwrite {reading and writing an rtree at the same time}</tcl>
<h2>Reading And Writing At The Same Time</h2>

<p>
It is the nature of the Guttman R-Tree algorithm that any write might
radically restructure the tree, and in the process change the scan order
of the nodes.  For this reason, it is not generally possible to modify
the R-Tree in the middle of a query of the R-Tree.  Attempts to do so
will fail with a [SQLITE_LOCKED] "database table is locked" error.

<p>
So, for example, suppose an application runs one query against an R-Tree like
this:

<codeblock>
SELECT id FROM demo_index
 WHERE maxY&gt;=35.0  AND minY&lt;=35.0;
</codeblock>

<p>
Then for each "id" value returned, suppose the application creates an 
UPDATE statement like the following and binds the "id" value returned against
the "?1" parameter:

<codeblock>
UPDATE demo_index SET maxY=maxY+0.5 WHERE id=?1;
</codeblock>

<p>
Then the UPDATE might fail with an SQLITE_LOCKED error.  The reason is that
the initial query has not run to completion.  It is remembering its place
in the middle of a scan of the R-Tree.  So an update to the R-Tree cannot
be tolerated as this would disrupt the scan.

<p>
It is also possible to express this kind of simultaneous read and write
on an R-Tree within a single query, for example if an UPDATE statement
tries to change the value of one row of the R-Tree based on a complicated
query from another row of the same R-Tree, perhaps something like this:

<codeblock>
UPDATE demo_index 
   SET maxY = (SELECT max(maxX) FROM demo_index AS x2
                WHERE x2.maxY&gt;demo_index.x2)
 WHERE maxY&gt;=35.0  AND minY&lt;=35.0;
</codeblock>

<p>
This is a limitation of the R-Tree extension only.  Ordinary tables in
SQLite are able to read and write at the same time.  Other virtual tables
might (or might not) also that capability.  And R-Tree can appear to read
and write at the same time in some circumstances, if it can figure out how
to reliably run the query to completion before starting the update.  But
you shouldn't count on that for every query.  Generally speaking, it is
best to avoid running queries and updates to the same R-Tree at the same
time.

<p>
If you really need to update an R-Tree based on complex queries against
the same R-Tree, it is best to run the complex queries first and store
the results in a temporary table, then update the R-Tree based on the values
stored in the temporary table.

<h1>Using R*Trees Effectively</h1>

<p>
For SQLite versions prior to 3.24.0 ([dateof:3.24.0]),
the only information that an R*Tree index stores about an object is
its integer ID and its bounding box.  Additional information needs to