On Mon, Jan 11, 2016 at 11:00 AM, Felipe Gasper <felipe at felipegasper.com> wrote:
> On 11 Jan 2016 1:45 PM, Scott Hess wrote: > >> As far as preventing the other process from using it before the schema >> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >> the schema does not exist. If you create the schema as a transaction, >> that >> will be atomic. >> > > But in order for that SELECT to avert TOCTTOU errors, we?d have to do > BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. > That would seem to get expensive? The solution really depends on what you're doing. You only need to verify that the schema exists once, if you check and find that it doesn't exist, you shouldn't be using it, if you check and find that it does exist, then it exists until you take action to change that situation. Presumably right now you have some sort of startup loop which looks like "Does the database exist? If not, sleep and check again." I'd just change that to "Does the schema exist? If not, sleep and check again." Note that the sqlite_master table is held in the same page as the SQLite header, unless it grows large enough to split into multiple pages. Also note that any use of the database tables will require reading the schema page. So querying the sqlite_master table at startup will not add any additional costs. Another option would be to use PRAGMA user_version, to signal from the schema-creation process to the schema-using process. I don't think it's really any better than querying the schema, though. Another option would be to just prepare a statement and if it fails with SQLITE_ERROR, that's probably because the relevant schema doesn't exist. So sleep and try again later. -scott
