Re: [sqlite] Unexpected "no such table" error.
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]
-
Re: [sqlite] Unexpected "no such table" error.
"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.
See also ticket #2486
http://www.sqlite.org/cvstrac/tktview?tn=2486
We have not plans to change any of these in the near future.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Unexpected "no such table" error.
So I wonder if "select null from sqlite_master limit 1" is the
cheapest, fastest, dirtiest way to make it work right now.
On 7/11/07, Joe Wilson <[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.
>
> Uncommenting the commented line fixes things right up, presumably
> because it forced sqlite to re-read things.
Looks similar to http://www.sqlite.org/cvstrac/tktview?tn=2486
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives.
http://tools.search.yahoo.com/toolbar/features/mail/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Ryan M. Lederman
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Unexpected "no such table" error.
Aha, yes, that does sound like exactly it. Thanks!
On 7/11/07, Joe Wilson <[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.
>
> Uncommenting the commented line fixes things right up, presumably
> because it forced sqlite to re-read things.
Looks similar to http://www.sqlite.org/cvstrac/tktview?tn=2486
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives.
http://tools.search.yahoo.com/toolbar/features/mail/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Unexpected "no such table" error.
--- 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.
>
> Uncommenting the commented line fixes things right up, presumably
> because it forced sqlite to re-read things.
Looks similar to http://www.sqlite.org/cvstrac/tktview?tn=2486
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives.
http://tools.search.yahoo.com/toolbar/features/mail/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Unexpected "no such table" error.
Hmm, I wonder if this is the same problem I'm having. I randomly get
"no such table" errors too. Anyone have experience with this
particular problem?
On 7/11/07, 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.
Uncommenting the commented line fixes things right up, presumably
because it forced sqlite to re-read things.
-scott
[Sorry if I'm missing something. Feels like I'm missing something.
But I can't see what :-).]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Ryan M. Lederman
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Unexpected "no such table" error.
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.
Uncommenting the commented line fixes things right up, presumably
because it forced sqlite to re-read things.
-scott
[Sorry if I'm missing something. Feels like I'm missing something.
But I can't see what :-).]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
