Re: [sqlite] Tracking database corruption

[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 number of failures around btreeInitPage.
> However when I pull up the saved copies I make whenever corruption occurs
> and run "pragma integrity_check", its coming up as "ok".
>
>
> I am not at this time keeping hold of the WAL file though, so wanted to know
> if I can confirm that the corruption was in the WAL file or if something
> else might be at fault.
>

It would be helpful if you could preserve the -wal file too.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tracking database corruption

[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 file too ?  
> Or the SHM file too ?

As for what I'm saving off, I do a byte for byte copy on the database files 
that were used in the statement that detected corruption (this is automated). 
I'm not copying the "-shm" or "-wal" files (an oversight which I'll fix). I was 
hoping to figure out if its likely capturing the "-wal" file would find the 
corruption, or if I could determine that the page its failing on was from the 
file I have (in which case something else is presumably going wrong).


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 recent addition is 
mmap (along with an upgrade from 3.8.8.3 to 3.16.2).


These databases are stored on a local disk (admittedly using an encrypting 
loop-back adapter). I doubt that I'll necessarily be able to track down what is 
causing the corruption given how infrequently it ends up occurring, storing the 
corrupt copies is my attempt at being able to track them down (since they are 
very infrequent and automatically "recovered" from).


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Monday, November 6, 2017 3:53:41 PM
To: SQLite mailing list
Subject: Re: [sqlite] Tracking database corruption

On 6 Nov 2017, at 10:38pm, 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?

Obtain the extended error code to learn more than you already know:




> Specifically I've been seeing a number of failures around btreeInitPage. 
> However when I pull up the saved copies I make whenever corruption occurs and 
> run "pragma integrity_check", its coming up as "ok".

What exactly are you "saving" ?  The database file ?  Or the WAL file too ?  Or 
the SHM file too ?

It would probably be useful to figure out what’s causing your corruption.  What 
PRAGMAs does your program use under normal use ?  Are you using anything clever 
like shared cache ?  Also, is your database stored on a disk in the computer 
running SQLite or it is accessed remotely, perhaps across a network ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments may contain confidential and privileged material 
for the sole use of the intended recipient. Any review, copying, or 
distribution of this email (or any attachments) by others is prohibited. If you 
are not the intended recipient, please contact the sender immediately and 
permanently delete this email and any attachments. No employee or agent of TiVo 
Inc. is authorized to conclude any binding agreement on behalf of TiVo Inc. by 
email. Binding agreements with TiVo Inc. may only be made by a signed written 
agreement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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: 
https://en.wikipedia.org/wiki/Sort-merge_join. It is a linear time operation.


What sqlite does now is for each "a" it searches through the index for "x". 
This search operation is logarithmic time. If there are many records in tab1, 
then this stacks and becomes quasilinear time. I experience this constantly 
with sqlite data wrangling and tab1 and 2 in the millions. sqlite's nested 
loops are very fast, but the joins _could_ be made much faster with merge joins 
in such situations. I just wish I had this hint...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tracking database corruption

[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.  https://www.sqlite.org/errlog.html

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 faster ?

There are specific cases where a merge join is faster than using JOIN … ORDER 
BY.  For that to happen, both source tables must already have indexes ideally 
suited to the merge join, and the rows which you’re going to want returned must 
be a very large proportion of both source tables, probably the whole tables.  
Also, SQLite has to be aware of those facts, it cannot simply assume them.

Except for the above cases, existing formats will be just as fast, and can be 
far faster, especially in cases where the rows wanted do not represent most of 
the rows of the existing tables.

Merge joins also represent a problem where you have to compare the two 
available rows.  There’s no good way to know what the programmer means by this, 
especially in cases involving many columns and collation methods.  Assumptions 
have to be made and whatever the development team picks is sure to annoy some 
users.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tracking database corruption

[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 recent addition is 
> mmap (along with an upgrade from 3.8.8.3 to 3.16.2).

Try not using memory mapping.  I don’t have a specific reason to think it’s 
causing the problem but it’s easy to turn off without modifying much of your 
source code, and it has caused problems on one platform in the past.

> These databases are stored on a local disk (admittedly using an encrypting 
> loop-back adapter). I doubt that I'll necessarily be able to track down what 
> is causing the corruption given how infrequently it ends up occurring, 
> storing the corrupt copies is my attempt at being able to track them down 
> (since they are very infrequent and automatically "recovered" from).

Encrypting adapters should not be causing a problem.

I forgot to ask whether you’re using simultaneously accessing the database from 
multiple threads or processes or applications.  If you’re doing this, please 
give details, including of your disk format, since this can be an easy cause of 
corruption.  If not, this simplifies things considerably.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 single process, so it's really easy, just clear
the cache on every write. However, I want to be prepared for the near
future where I will have multiple processes using this db file.

I'm thinking that to detect writes, this might be a safe approach:

Before serving any cached read, check the timestamp on the wal file. If it
changed, read the global version. If it changed, clear the cache.
Otherwise, serve the cached read.

Is it safe to assume that all writes would mean change of the wal file
timestamp?
More importantly, is it faster to check the timestamp or would a prepared
query for the version actually be faster (and safer)?

Also, I'm using WAL right now, but I wonder if that's really useful given
the single-writer-at-a-time?

Thank you for your insights,

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 list
Subject: [sqlite] Most efficient way to detect on-disk change

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 single process, so it's really easy, just clear
the cache on every write. However, I want to be prepared for the near
future where I will have multiple processes using this db file.

I'm thinking that to detect writes, this might be a safe approach:

Before serving any cached read, check the timestamp on the wal file. If it
changed, read the global version. If it changed, clear the cache.
Otherwise, serve the cached read.

Is it safe to assume that all writes would mean change of the wal file
timestamp?
More importantly, is it faster to check the timestamp or would a prepared
query for the version actually be faster (and safer)?

Also, I'm using WAL right now, but I wonder if that's really useful given
the single-writer-at-a-time?

Thank you for your insights,

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 those (structured as above) but I'm now seeing some:

automatic index on mi(ID)
automatic index on mi(ID)
automatic index on tal(ID)
automatic index on mi(ID)
automatic index on tal(ID)

Are those 'mi' and 'tal' meaningful and could the 'ID' in parenthesis be linked 
to some 'ID' in my schemas or are they related to SQLite temporaries?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tracking database corruption

[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 specific reason to think it’s 
>causing the problem but it’s easy to turn off without modifying much of your 
>source code, and > it has caused problems on one platform in the past.

I only recently turned it on in order to relieve memory pressure from other 
parts of the system. Given how low the frequency is (something in the 
thousandths of a percent chance in a given day for a given device [It was 
~0.0073%, its currently ~0.0185%), its hard to know when there is a signal vs 
just noise.

>I forgot to ask whether you’re using simultaneously accessing the database 
>from multiple threads or processes or applications.  If you’re doing this, 
>please give details, including of your disk format, since this can be an easy 
>cause of corruption.  If not, this simplifies things considerably.

I am using multiple threads and processes to access the databases. The library 
is configured as multi-thread, with the connection handles mutexed by the 
application. Most statements have their sqlite3_stmt get cached (and these 
caches are per-connection handle since the prepared statements only work 
against a given handle).  The page size is configured at 4k (although I suppose 
that is the default now). The database files grow in 1 MiB increments (using 
sqlite3_file_control). Most database handles have multiple databases attached 
(with main being a database with an empty schema).

Having multiple handles per database is in part why switching to mmap saved 
some memory pressure (since page cache isn't shared without shared_cache).

Disk is formatted as ext3 (linux 2.6.31 kernel; relevant mount options: 
relatime,errors=continue,barrier=1,data=ordered).  Writes are not actually 
getting coalesced by the kernel disk scheduling (there are reasons for this, 
but none of which are relevant for this).




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Tuesday, November 7, 2017 8:43 AM
To: SQLite mailing list
Subject: Re: [sqlite] Tracking database corruption



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 recent addition is 
> mmap (along with an upgrade from 3.8.8.3 to 3.16.2).

Try not using memory mapping.  I don’t have a specific reason to think it’s 
causing the problem but it’s easy to turn off without modifying much of your 
source code, and it has caused problems on one platform in the past.


> These databases are stored on a local disk (admittedly using an encrypting 
> loop-back adapter). I doubt that I'll necessarily be able to track down what 
> is causing the corruption given how infrequently it ends up occurring, 
> storing the corrupt copies is my attempt at being able to track them down 
> (since they are very infrequent and automatically "recovered" from).

Encrypting adapters should not be causing a problem.

I forgot to ask whether you’re using simultaneously accessing the database from 
multiple threads or processes or applications.  If you’re doing this, please 
give details, including of your disk format, since this can be an easy cause of 
corruption.  If not, this simplifies things considerably.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments may contain confidential and privileged material 
for the sole use of the intended recipient. Any review, copying, or 
distribution of this email (or any attachments) by others is prohibited. If you 
are not the intended recipient, please contact the sender immediately and 
permanently delete this email and any attachments. No employee or agent of TiVo 
Inc. is authorized to conclude any binding agreement on behalf of TiVo Inc. by 
email. Binding agreements with TiVo Inc. may only be made by a signed written 
agreement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 single process, so it's really easy, just clear
> the cache on every write. However, I want to be prepared for the near
> future where I will have multiple processes using this db file.

Documentation for the PRAGMA says

" The "PRAGMA data_version" value is a local property of each database 
connection and so values returned by two concurrent invocations of "PRAGMA 
data_version" on separate database connections are often different even though 
the underlying database is identical. "

So when you convert your code to use multiple processes, they must all use the 
same connection for this to work properly.  That means you will have to invent 
a method to ensure that only one of them makes changes at a time.  That might 
not be what you wanted to do.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tracking database corruption

[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.

Of the two, (A) is almost trivial to check.  Turn it off, and see whether the 
fault continues to occur.  I’m aware this will make your program use more 
time/resources, but it’s just for temporary testing.

Testing (B) is more difficult and requires understanding of your own specific 
program design.  You should only have to worry about parts of your code which 
write to the database.  Pay special attention to whether you’re sharing SQLite 
connections or not.  This is so difficult to debug you might try the other 
things mentioned here before you investigate this problem.

> Disk is formatted as ext3 (linux 2.6.31 kernel; relevant mount options: 
> relatime,errors=continue,barrier=1,data=ordered).  Writes are not actually 
> getting coalesced by the kernel disk scheduling (there are reasons for this, 
> but none of which are relevant for this).


I’m not aware of problems on ext3 with barrier=1.  However, you might consider 
trying errors=panic for a while.  This is something I recommend for all servers 
since it allows you to identify faults far more quickly than trying to 
reverse-engineer corruption reports.

In closing, please note that a good proportion of the data-corruption problems 
reported here turn out to be caused by hardware problems.  Some are in 
motherboard data channels, others in a storage subsystem.  Consider that all 
your code may be faultless, SQLite may be faultless, and that the problem is in 
hardware.

You might learn something more from section 6 of



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 connection. It doesn’t matter if those values are different in 
different processes.

(In any case, it’s impossible for multiple processes to [directly] use the same 
SQLite connection, since a connection is an in-memory object.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 (application) cache provide that is not 
provided by the two lower level caches?  In other words if the data is already 
stored in the page cache and (that which isn't) is already in the OS file cache 
(which it must be since you read the data once already), what benefit does the 
third-level cache provide other than add the overhead and complication of its 
management?

Likely the most "efficient" way to ensure you do not serve stale data is to get 
rid of the application level caching and simply re-retrieve the data when you 
need it.  Unless of course you are seriously memory constrained such at running 
this on a model 5150.  Or your query's are very long and complicated.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Tuesday, 7 November, 2017 11:08
>To: SQLite mailing list
>Subject: [sqlite] Most efficient way to detect on-disk change
>
>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 single process, so it's really easy, just
>clear
>the cache on every write. However, I want to be prepared for the near
>future where I will have multiple processes using this db file.
>
>I'm thinking that to detect writes, this might be a safe approach:
>
>Before serving any cached read, check the timestamp on the wal file.
>If it
>changed, read the global version. If it changed, clear the cache.
>Otherwise, serve the cached read.
>
>Is it safe to assume that all writes would mean change of the wal
>file
>timestamp?
>More importantly, is it faster to check the timestamp or would a
>prepared
>query for the version actually be faster (and safer)?
>
>Also, I'm using WAL right now, but I wonder if that's really useful
>given
>the single-writer-at-a-time?
>
>Thank you for your insights,
>
>Wout.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 change
and can prevent arbitrary polling

On Tue, Nov 7, 2017 at 10:07 AM, 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 the most efficient way to make sure I *never* serve stale
> data?
>
> Right now everything's a single process, so it's really easy, just clear
> the cache on every write. However, I want to be prepared for the near
> future where I will have multiple processes using this db file.
>
> I'm thinking that to detect writes, this might be a safe approach:
>
> Before serving any cached read, check the timestamp on the wal file. If it
> changed, read the global version. If it changed, clear the cache.
> Otherwise, serve the cached read.
>
> Is it safe to assume that all writes would mean change of the wal file
> timestamp?
> More importantly, is it faster to check the timestamp or would a prepared
> query for the version actually be faster (and safer)?
>
> Also, I'm using WAL right now, but I wonder if that's really useful given
> the single-writer-at-a-time?
>
> Thank you for your insights,
>
> Wout.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 the most efficient way to make sure I *never* serve
> stale data?
> 
> Right now everything's a single process, so it's really easy, just
> clear the cache on every write. However, I want to be prepared for
> the near future where I will have multiple processes using this db
> file.
> 
> I'm thinking that to detect writes, this might be a safe approach:
> 
> Before serving any cached read, check the timestamp on the wal file.
> If it changed, read the global version. If it changed, clear the
> cache. Otherwise, serve the cached read.
> 
> Is it safe to assume that all writes would mean change of the wal file
> timestamp?
> More importantly, is it faster to check the timestamp or would a
> prepared query for the version actually be faster (and safer)?
> 
> Also, I'm using WAL right now, but I wonder if that's really useful
> given the single-writer-at-a-time?

You can define triggers on insert, update and delete that fires a user defined 
function that warns your other threads or an external process (I use a similar 
setup on a AS400/DB2). 
Something like this:

CREATE TRIGGER tg_night_watcher_insert AFTER INSERT ON table_name_to_watch_up
 BEGIN
  SELECT your_nigth_watcher();
 END

HTH

> 
> Thank you for your insights,
> 
> Wout.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 expensive to recompute; a common example is partial or full web 
pages. Rendering image, like graphs, can also be expensive. Or hey, how about 
the results of a complex SQL query?

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 presumptuous here. It’s pretty common for presentation-level 
> data to be very expensive to recompute; a common example is partial or full 
> web pages. Rendering image, like graphs, can also be expensive. Or hey, how 
> about the results of a complex SQL query?

It’s caching at two different levels.  The level Keith is talking about happens 
when you submit a SQL query and cache the data retrieved.  The level Jens is 
talking about happens when you submit a SQL query, process the data, then cache 
the results of the processing.

And it’s all highly dependent on the hardware, the amount of data and the type 
of processing.  And we don’t know whether the OP did premature optimisation.  
So let’s forget this threadlet until and unless we have relevant data.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Next release add flag for building LSM1?

[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.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 original post:


Right now everything's a single process, so it's really easy, just clear
the cache on every write. However, I want to be prepared for the near
future where I will have multiple processes using this db file.

Documentation for the PRAGMA says

" The "PRAGMA data_version" value is a local property of each database connection and so 
values returned by two concurrent invocations of "PRAGMA data_version" on separate database 
connections are often different even though the underlying database is identical."

So when you convert your code to use multiple processes, they must all use the 
same connection for this to work properly.  That means you will have to invent 
a method to ensure that only one of them makes changes at a time.  That might 
not be what you wanted to do.



I think it's the opposite. For connection A, the value of "PRAGMA 
data_version" does not change as a result of commits by connection A. It 
changes if the db is modified by any other connection, regardless of 
whether or not that other connection resides in a different process or not.


"The integer values returned by two invocations of "PRAGMA data_version" 
from the same connection will be different if changes were committed to 
the database by any other connection in the interim. The "PRAGMA 
data_version" value is unchanged for commits made on the same database 
connection."


Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wout Mertens
Gesendet: Dienstag, 07. November 2017 19:08
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Most efficient way to detect on-disk change

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 single process, so it's really easy, just clear the 
cache on every write. However, I want to be prepared for the near future where 
I will have multiple processes using this db file.

I'm thinking that to detect writes, this might be a safe approach:

Before serving any cached read, check the timestamp on the wal file. If it 
changed, read the global version. If it changed, clear the cache.
Otherwise, serve the cached read.

Is it safe to assume that all writes would mean change of the wal file 
timestamp?
More importantly, is it faster to check the timestamp or would a prepared query 
for the version actually be faster (and safer)?

Also, I'm using WAL right now, but I wonder if that's really useful given the 
single-writer-at-a-time?

Thank you for your insights,

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 key, b);
sqlite> explain query plan select * from t4, t3 where t3.b = t4.b;
0|0|0|SCAN TABLE t4
0|1|1|SEARCH TABLE t3 USING AUTOMATIC COVERING INDEX (b=?)

Even if I inserted ~100 rows in t3 and t4, query planner anyways
wouldn't use automatic indexes. So, why tables without rowid can't use
automatic indexes optimization?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users