IssueTracker

Can you provide a bit more details how PRAGMA wal_autocheckpoint is failing to work in onOpen? When you run the PRAGMA statement again later is the result different than what you expected?

Room does not expose onConfigure but you can pass an open helper factory that creates an open helper that implements onConfigure, see: https://developer.android.com/reference/kotlin/androidx/room/RoomDatabase.Builder#openhelperfactory, you probably also want to delegate to the default helper so when you build your factory use a FrameworkSQLiteOpenHelperFactory to create the helper to delegate to the other methods you're not interested on. Let me know if this helps.

Actually, looking at the framework's binding implementation it seems every time a new connection is opened, the WAL auto checkpoint is configured which might explain why your PRAGMA is not working. Since Android and Room uses a shared pool of connections and wal_autocheckpoint is a per connection configuration, you essentially have to execute that PRAGMA before any query, since neither the framework or Room offers a callback for configuring an individual SQLiteConnection. So I'm not sure you'll be able to successfully and nicely disable auto checkpoint.

I'm curious now as to why do you want to disable auto checkpoints? If it's possible to know that is.

The reason is that I have a big transaction that needs to be written (> 20K rows after establishing relations), however after the transaction finishes since the WAL Journal exceeded the autocheckpoint threshold, the SQLite tries to write back the Journal to the DB file, which could take roughly 5 seconds, and during that time the Database is locked from reading and writing, which results in an unpleasant UX as the user waits for 5 seconds until he can be able to see the details of the item.

I tried to commit smaller transactions by chunking the rows into smaller rows, but this made the writing to take more than it should, and also didn't solve the DB read/write block issue part entirely, especially with Android defining the autocheckpoint at 100 pages[1] rather than the default SQLite 1000 pages[2].

So my plan was to disable auto checkpointing entirely, and Schedule a Periodic manual checkpoint that should run as a background task through WorkManager and runs when the app is in background. However as you've mentioned the PRAGMA needs to be executed per connection, and not per open, and that's not supported in Room, or the Framework's SQLite code.

There is a new API introduced since API 30 that exactly solves such a case I'm facing, but not sure if it can be backported. [3]

Right now I've managed to solve my problem by using Requery's version of SQLite since it has better defaults than the Framework's[4], and with that it solved the writing performance that I'm facing, at the expense of the APK size unfortunately.

I think you can close this issue since it won't really solve the use case.

[1] https://cs.android.com/android/platform/superproject/+/master:frameworks/base/core/res/res/values/config.xml;l=1920;drc=master;bpv=1;bpt=0

[2] https://www.sqlite.org/c3ref/wal_autocheckpoint.html

[3] https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#execPerConnectionSQL(java.lang.String,%20java.lang.Object[])

[4] https://github.com/requery/sqlite-android/pull/119

I see... Indeed execPerConnectionSQL would be ideal and was actually introduced for this and similar reasons (to let user apply per connection configurations), but it is tied to the native bindings in the framework. I don't think we can backport it to older APIs, but we can update the SupportSQLiteDatabase interface so that unbundled SQLite implementations such as Requery's sqlite-android can implement them and you would be able to take advantage of it. I'll go ahead and start that change and once in we can ask Requery to implement it.

Thanks for your reply! I think adding it to the SupportSQLiteDatabase would emphasize its importance so that third party SQLite providers like Requery might take it into account and provide a backport that would work on pre-API 30 levels. I've already opened an issue there asking them to support it, but that was before our conversation.[1] If you don't mind I'll link your comment there. (I'll wait for your OK though before doing that)

[1] https://github.com/requery/sqlite-android/issues/135