[odb-users] Questions about releasing/closing CONNECTION POOL under WAL mode

[Reilly He]

Dear Boris,

The only way to release/close connections is to destroy the corresponding
> connection object. So in your case the natural thing to do if you want to
> explicitly close both types of connections is to destroy your two pools:
>
> m_Reader.reset ();
> m_Writer.reset ();
>
 Got it, this is *exactly what we did* in order to close the connection
pool.

An alternative to
> closing everything would be to force the WAL checkpointing using
> sqlite3_wal_checkpoint_v2()[1] after each write batch.
>
Speaking about checkpoints, we are using auto_checkpoint
<https://www.sqlite.org/capi3ref.html#sqlite3_wal_autocheckpoint> with a
threshold of *150* sqlite pages.

But generally, if your storage is not "sane", it will be hard to
> achieve 100% reliability and your only recourse is to try to minimize
> corruptions.
>
And I have heard some *dirty tricks* that a harddrive might play (i.e.,
return "finished" for flushing data while it has not even begun).
So the storage might actually be an issue for Android users. We will keep
looking for any further evidence to proof that.

Thanks a lot for the help boris.

Best Regards,
Reilly He

On Wed, Apr 26, 2023 at 9:14 PM Boris Kolpackov <boris at codesynthesis.com>
wrote:

> Reilly He <reillyhekazusa at gmail.com> writes:
>
> > > class RWConnectionPool: public odb::sqlite::connection_factory
> > > {
> > >     typedef std::unique_ptr<odb::sqlite::connection_pool_factory>
> ReaderPool;
> > >     typedef std::unique_ptr<odb::sqlite::single_connection_factory>
> WriterPool;
> > >
> > >     [...]
> > >
> > > protected:
> > >     ReaderPool m_Reader{};
> > >     WriterPool m_Writer{};
> > > };
> > >
> >
> > I wanna make sure whether we should release/close all the connections in
> > these 2 pools *explicitly*?
>
> The only way to release/close connections is to destroy the corresponding
> connection object. So in your case the natural thing to do if you want to
> explicitly close both types of connections is to destroy your two pools:
>
> m_Reader.reset ();
> m_Writer.reset ();
>
>
> > Our program also runs on *Android* platform, and in android platform, we
> > got NO hint/callbacks before process exiting. So in this case, the pool's
> > *deallocator* will not be working (since user just kill our program`s
> > process), is that OK? Since we released this feature to the market, we
> > received 17 Android database corruption issues compared to just 1 iOS
> > database corruption issue on IOS.
>
> SQLite should normally be able to handle this provided the underlying
> storage is "sane" (i.e., actually durable, not lying to SQLite that a
> write has been committed to physical media, etc).
>
> If that's not the case and there is no notification for a graceful exit,
> then I believe your only option is to only hold open transactions and
> connections while writing, closing everything as soon as you are done,
> and then re-opening when you need to write again. An alternative to
> closing everything would be to force the WAL checkpointing using
> sqlite3_wal_checkpoint_v2()[1] after each write batch.
>
> But generally, if your storage is not "sane", it will be hard to
> achieve 100% reliability and your only recourse is to try to minimize
> corruptions.
>
> [1] https://www.sqlite.org/wal.html#application_initiated_checkpoints
>