Hello @pocketpixels,

[Dark403969]

Hello @pocketpixels,

This is a known behavior of the GRDB 5 DatabasePool that I'm still struggling to avoid.

It is not strictly a bug, because the documentation carefully says that ValueObservation can notify duplicates: apps should be ready for such stuttering.

But just as you say, this can create performance issues. And this is just weird. And once you notice it, you can't forget it. Meh.

There is a benefit, though. When you start a ValueObservation on a DatabasePool, you are now guaranteed to get the initial value shortly, even if there is a long write transaction in the background. With GRDB 4, observations could only start after all pending writes were completed, and this could create a really bad user experience. Slow writes happen, for example, in apps that sync a lot of data with a remote server, and those could not observe and quickly display data on screen until those long writes would complete (#601, addressed by #736).

So for one win (butter-smooth observations), we get one loss (double notification of the initial value).

There is already a fix, for custom SQLite builds compiled with the SQLITE_ENABLE_SNAPSHOT option. GRDB can then avoid the double initial notification. This option is unfortunately not available on the stock SQLite version that ships with iOS and watchOS. Well, it's there, but unusable, as cruelly revealed by #846.

The difficulty is that when you start a ValueObservation:

1. An initial value is fetched as soon as possible from a reader SQLite connection, regardless of concurrent writes. In your case, this initial fetch is synchronous (.immediate).
2. The observation then waits for a slot in the writer SQLite connection
3. From the writer SQLite connection, it can hook on SQLite commits and spot changes that impact the observed region. From now on, no change will be missed: the observation has opened its eyes.
4. Between 1 and 3, the database could have been modified, and nobody was aware of it.
5. Hence the security double fetch, just in case we missed some changes.

On step (4), if we can prove for sure that between the initial read in a reader connection (1), and the setup of the commit hooks on the writer connection (3), no change was performed, then we can avoid the double notification.

It's possible with SQLITE_ENABLE_SNAPSHOT.

Without this option, I could never manage to do it. Some serious extra SQLite mojo is needed 😅

Originally posted by @groue in https://github.com/groue/GRDB.swift/issues/937#issuecomment-794102361

[Dark403969]

Hello @pocketpixels,

This is a known behavior of the GRDB 5 DatabasePool that I'm still struggling to avoid.

It is not strictly a bug, because the documentation carefully says that ValueObservation can notify duplicates: apps should be ready for such stuttering.

But just as you say, this can create performance issues. And this is just weird. And once you notice it, you can't forget it. Meh.

There is a benefit, though. When you start a ValueObservation on a DatabasePool, you are now guaranteed to get the initial value shortly, even if there is a long write transaction in the background. With GRDB 4, observations could only start after all pending writes were completed, and this could create a really bad user experience. Slow writes happen, for example, in apps that sync a lot of data with a remote server, and those could not observe and quickly display data on screen until those long writes would complete (#601, addressed by #736).

So for one win (butter-smooth observations), we get one loss (double notification of the initial value).

There is already a fix, for custom SQLite builds compiled with the SQLITE_ENABLE_SNAPSHOT option. GRDB can then avoid the double initial notification. This option is unfortunately not available on the stock SQLite version that ships with iOS and watchOS. Well, it's there, but unusable, as cruelly revealed by #846.

The difficulty is that when you start a ValueObservation:

1. An initial value is fetched as soon as possible from a reader SQLite connection, regardless of concurrent writes. In your case, this initial fetch is synchronous (.immediate).
2. The observation then waits for a slot in the writer SQLite connection
3. From the writer SQLite connection, it can hook on SQLite commits and spot changes that impact the observed region. From now on, no change will be missed: the observation has opened its eyes.
4. Between 1 and 3, the database could have been modified, and nobody was aware of it.
5. Hence the security double fetch, just in case we missed some changes.

On step (4), if we can prove for sure that between the initial read in a reader connection (1), and the setup of the commit hooks on the writer connection (3), no change was performed, then we can avoid the double notification.

It's possible with SQLITE_ENABLE_SNAPSHOT.

Without this option, I could never manage to do it. Some serious extra SQLite mojo is needed 😅

Originally posted by @groue in groue/GRDB.swift#937 (comment)

help

[Dark403969]

3