Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Eduardo Morras
On Tue, 07 Nov 2017 18:07:42 + Wout Mertens wrote: > I'm working with a db that's only written to in transations, and each > transaction increases a db-global version counter. > > This means that I can cache all reads, unless the version changed. > > What would be

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Simon Slavin
On 7 Nov 2017, at 11:12pm, Jens Alfke wrote: > On Nov 7, 2017, at 2:39 PM, Keith Medcalf wrote: > >> What advantage does your third-level (application) cache provide that is not >> provided by the two lower level caches? > > You’re being

[sqlite] Next release add flag for building LSM1?

2017-11-07 Thread Charles Leifer
I was wondering if there were plans for adding a simple toggle for building the LSM virtual table? For example, to build json1, one can: export CFLAGS="... -DSQLITE_ENABLE_JSON1 ..." Be handy to have a SQLITE_ENABLE_LSM1 as well. Thanks for all your hard work.

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Dan Kennedy
On 11/08/2017 02:41 AM, Simon Slavin wrote: On 7 Nov 2017, at 6:53pm, David Raymond wrote: I think pragma data_version is what you're looking for. http://www.sqlite.org/pragma.html#pragma_data_version An excellent solution, though there’s a /caveat/. From the

Re: [sqlite] [EXTERNAL] Most efficient way to detect on-disk change

2017-11-07 Thread Hick Gunter
There is also http://sqlite.org/pragma.html#pragma_user_version which more closely resembles what you have now. I strongly suspect that an update cycle of the user_version should be done within the transaction performing the changes. BEGIN Read user version Write updated user version COMMIT

Re: [sqlite] Tracking database corruption

2017-11-07 Thread Simon Slavin
Given what you wrote, and assuming you are not accidentally writing to memory or file handles maintained by SQLite, your corruption is probably caused by A) Faulty implementation of memory mapping B) Multiple thread/processes writing to the database at one time, through some fault in mutexing.

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread J Decker
in linux inotify - http://man7.org/linux/man-pages/man7/inotify.7.html in windows FindFirstChangeNotification/FindNextChangeNotification https://msdn.microsoft.com/en-us/library/windows/desktop/aa365261(v=vs.85).aspx if you wait for an actual change before checking to see if there really was a

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Jens Alfke
> On Nov 7, 2017, at 11:41 AM, Simon Slavin wrote: > > So when you convert your code to use multiple processes, they must all use > the same connection for this to work properly. No; it just means each process will track its own data-version value based on its own

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Keith Medcalf
So you are caching data at the application level that is cached at the database page cache level which is cached in the Operating System file cache that lives in a file residing on disk -- effectively storing three copies of the data in memory. What advantage does your third-level

Re: [sqlite] Tracking database corruption

2017-11-07 Thread Richard Hipp
On 11/6/17, Nicholas Lovell wrote: > When getting sqlite_corrupt when reading from a database, is there a way to > know if a particular failure came from reading/initializing a page from a > WAL file, or the database disk file? > > > Specifically I've been seeing a

Re: [sqlite] Tracking database corruption

2017-11-07 Thread Nicholas Lovell
There aren't any extended error codes for sqlite_corrupt. Its just error code 11 "database disk image is malformed". I have the line numbers where the corruption is getting hit (they vary, but several are in btreeInitPage) > What exactly are you "saving" ? The database file ? Or the WAL

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Jens Alfke
> On Nov 7, 2017, at 2:39 PM, Keith Medcalf wrote: > > What advantage does your third-level (application) cache provide that is not > provided by the two lower level caches? You’re being presumptuous here. It’s pretty common for presentation-level data to be very

[sqlite] Automatic indexes don't work on without rowid tables

2017-11-07 Thread korablev
sqlite> create table t1(a primary key, b) without rowid; sqlite> create table t2(a primary key, b) without rowid; sqlite> explain query plan select * from t1, t2 where t1.b = t2.b; 0|0|0|SCAN TABLE t1 0|1|1|SCAN TABLE t2 sqlite> create table t3(a primary key, b); sqlite> create table t4(a primary

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread David Raymond
I think pragma data_version is what you're looking for. http://www.sqlite.org/pragma.html#pragma_data_version -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens Sent: Tuesday, November 07, 2017 1:08 PM To: SQLite mailing

[sqlite] SQLite 3.21 - logging changed about some automatic indexes

2017-11-07 Thread Olivier Mascia
Hello, Before 3.21 I was regularly spotting some automatic index notices in the log like these: automatic index on sqlite_sq_FEB62D5CD0(ID) automatic index on sqlite_sq_FEB62D5100(ID) automatic index on sqlite_sq_FEB62D4A40(ID) automatic index on sqlite_sq_FEB62D5BB0(ID) Since 3.21 I don't see

Re: [sqlite] Tracking database corruption

2017-11-07 Thread Richard Hipp
On 11/6/17, Nicholas Lovell wrote: > There aren't any extended error codes for sqlite_corrupt. Its just error > code 11 "database disk image is malformed". Additional information is available if you use the error and warning log feature.

Re: [sqlite] Feature request: merge joins (preferably through a hint)

2017-11-07 Thread Davor Josipovic
> You are thinking that perhaps queries such as the following might > be faster using a merge: > > SELECT * FROM tab1 JOIN tab2 ON tab1.a=tab2.x; > > I disagree. I don't see any reason to disagree. Merge join will definitely be faster if the data is already sorted. See the reference:

[sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Wout Mertens
I'm working with a db that's only written to in transations, and each transaction increases a db-global version counter. This means that I can cache all reads, unless the version changed. What would be the most efficient way to make sure I *never* serve stale data? Right now everything's a

Re: [sqlite] Feature request: merge joins (preferably through a hint)

2017-11-07 Thread Simon Slavin
On 7 Nov 2017, at 7:59am, Davor Josipovic wrote: > What sqlite does now is for each "a" it searches through the index for "x". If an ideal index already exists, accessing the correct records will be fast. If one does not exist, how would you expect a merge join to be any

Re: [sqlite] Tracking database corruption

2017-11-07 Thread Simon Slavin
On 7 Nov 2017, at 1:11am, Nicholas Lovell wrote: > In terms of pragmas during normal usage: user_version, recursive_triggers, > cache_size, mmap_size (max size is set to 15 MiB; which covers the files in > question entirely), journal_mode (=wal). The only somewhat

Re: [sqlite] Tracking database corruption

2017-11-07 Thread Nicholas Lovell
I am using the errlog interface, which is why I have line numbers for where its occurring. The location varies with some clusters around R-47608-56469, and /* Freeblock off the end of the page */, although there are other locations as well. >Try not using memory mapping. I don’t have a

Re: [sqlite] Most efficient way to detect on-disk change

2017-11-07 Thread Simon Slavin
On 7 Nov 2017, at 6:53pm, David Raymond wrote: > I think pragma data_version is what you're looking for. > http://www.sqlite.org/pragma.html#pragma_data_version An excellent solution, though there’s a /caveat/. From the original post: > Right now everything's a