On 7/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
"Scott Hess" <[EMAIL PROTECTED]> wrote: > do_test test-1.1 { > execsql {PRAGMA encoding} > > sqlite3 db2 test.db > execsql {CREATE TABLE t (id int)} db2 > db2 close > > #execsql {SELECT * FROM sqlite_master} > catchsql {SELECT * FROM t} > } {1 {no such table: t}} > > Looks like the schema info is obviously being cached. The first > PRAGMA is to make sure this is happening - I originally wrote it with > the CREATE in db and the SELECT in db2, and the behaviour didn't > manifest unless I forced it to hit the database first. >Correct. The parser does not check to see if the database schema has changed. To do so would be a noticable performance impact and in most cases the schema does not change.
I can understand this position, but ... when you execute a query, you _must_ hit the database (both to check the schema cookie and to do the query), and if you assume that queries are prepared in order to be executed, then it seems likely that the overall performance impact might become pretty hard to notice. [Time would shift from the step to the prepare, but the overall time should be essentially the same.] Additional point is that in the case where a prepare worked but the schema changed before step, you can start over and after re-prepare things will work fine. If a prepare fails because a table doesn't exist, you cannot easily distinguish between simply-doesn't-exist and doesn't-exist-in-cached-schema. So you could reasonably argue that if the prepare works using the cached schema, but the actual schema differs, it can be handled as currently, but if the prepare doesn't work using the cached schema, a check for a difference with the actual schema would be helpful. I'm assuming, here, that the cost of this check is essentially "read the 4 bytes from offset 24 of page 1". If it's something more complicated, like "parse the schema", then, yeah, there's not going to be a very good solution under any circumstances! It seems like it would be interesting to have a PRAGMA or perhaps an option to BEGIN to handle this. In the PRAGMA case, the idea is that if the prepare throws an error, it would re-read the schema just in case - but the schema could change between prepare and step just as it could anywhere else (which would throw the schema error in step). The BEGIN option would be similar, except that it would only apply during the duration of the transaction. It might also force a shared lock at the point of the prepare, rather than at the point of the step. -scott ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
