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: |
a8afb2f9056eb408215c15c24659cfff |
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
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>=35.0 AND minY<=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>demo_index.x2) WHERE maxY>=35.0 AND minY<=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 |
︙ | ︙ |