Re: [sqlite] fsync on -wal still happening

One presumes that you mayhaps read the documentation? "In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before each checkpoint and the database file is synchronized after each completed checkpoint and the WAL file header is synchronized when a WAL file begins to be

Re: [sqlite] advice about schema versioning and WAL

Thanks Dan for this information! (For some reason, your reply appears on the mailing list archive, but never found the way of my inbox. I'm replying to my own message) Gwendal > Dan Kennedy wrote: > > "PRAGMA schema_version" should work the same way in WAL mode. The pragma > will read the

Re: [sqlite] fsync on -wal still happening

On Sat, 2017-12-30 at 05:40 +, Simon Slavin wrote: > Accumulate your change commands in memory as text.  Eventually > execute them all inside BEGIN … COMMIT. I like it; why didn't I think of it? Don't you love those moments? The application would need severe retooling for reads unless I also

Re: [sqlite] fsync on -wal still happening

On 30 Dec 2017, at 5:37am, Ian Freeman wrote: > Agreed, the defaults would be very safe. But I realize I never > mentioned what my actual goal was: minimizing disk writes for a write- > heavy application. That's why I want to run -wal out of memory, or set >

Re: [sqlite] fsync on -wal still happening

On Fri, 2017-12-29 at 22:18 +, Simon Slavin wrote: > > On 29 Dec 2017, at 7:07pm, Ian Freeman wrote: > > > Thanks, Simon. Indeed I did see that option. But I'm concerned > > about > > maintaining integrity in the middle of an power-interrupted commit, > > This is not a

Re: [sqlite] finding the number of records until a value is different

I think it's possible with CTE. Recently I wondered whether it would be possible to implement an operation that might be called "an accumulated group by". It's when you enumerate the rows and based on the values of the previous row and current row you apply some new "group" value that can be used

Re: [sqlite] fsync on -wal still happening

On 29 Dec 2017, at 7:07pm, Ian Freeman wrote: > Thanks, Simon. Indeed I did see that option. But I'm concerned about > maintaining integrity in the middle of an power-interrupted commit, This is not a problem for either WAL mode or the older journal modes. If you avoid

[sqlite] Dan. Latest zipfile.c checkin feedback

I tried the new write feature. Not sure if intended but read back row order is somewhat unintuitive. Is there an easy way to create a functional archive read back order without introducing sortable name prefix artifacts or an index file entry? CREATE VIRTUAL TABLE newzip USING

Re: [sqlite] fsync on -wal still happening

I believe the inotifywait does not actually wait for the fsync operation before notifying. Process A can write to a file, the OS can cache it without flushing to disk, and a request by process B will be served directly from the cache. Therefore the operating system can notify you of the change

Re: [sqlite] fsync on -wal still happening

Thanks, Simon. Indeed I did see that option. But I'm concerned about maintaining integrity in the middle of an power-interrupted commit, so I was hoping to leverage WAL's inverted behavior of not modifying the database file directly until checkpoint time. The only thing left to investigate is how

[sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Hi, I'm writing some software that can read data off GeoPackage (SQLite + rtree + standardized set of metadata tables) as an alternative format for spatial databases, like PostgreSql with the PostGIS extension. Now, when I use PostGIS the query plan optimizer checks the bbox provided in the query

Re: [sqlite] fsync on -wal still happening

On 29 Dec 2017, at 4:34pm, Ian Freeman wrote: > I see, then what I'm seeing is just normal behavior of the writes being > flushed to disk. I read what I wanted to hear about synchronous=NORMAL > delaying writes to the -wal file. Instead I'm going to see if I can > move -wal

Re: [sqlite] fsync on -wal still happening

I see, then what I'm seeing is just normal behavior of the writes being flushed to disk. I read what I wanted to hear about synchronous=NORMAL delaying writes to the -wal file. Instead I'm going to see if I can move -wal to a ramdisk and see how that will affect db integrity with power losses at

Re: [sqlite] fsync on -wal still happening

On 12/28/17, Ian Freeman wrote: > I have configured journal_mode=WAL, synchronous=NORMAL, and disabled > autocheckpointing, and yet the -wal file is modified after every update > operation... That is correct. "sync" means forcing the data from the internal operating-system

[sqlite] fsync on -wal still happening

I have configured journal_mode=WAL, synchronous=NORMAL, and disabled autocheckpointing, and yet the -wal file is modified after every update operation... That's not the documented behavior as I understand it. This smells like a bug to me, but before I go that direction, is there an explanation

[sqlite] The value of nFetchOut and zPath after wal-file calls sqlite3OsOpen()

I want to analyze the executive process of wal-file with the source code 3.16.2 . And I added: unixFile *pFd = (unixFile *)pRet->pWalFd; if(pFd->nFetchOut != 0){ printf("hi\n"); } to sqlite3WalOpen() after sqlite3OsOpen() is called: rc = sqlite3OsOpen(pVfs, zWalName,