Unexpected ValueObservation behaviour

[sobri909]

Hi! I'm getting some unexpected results from some ValueObservation code. It looks to me like user error, but I can't find a cause for it in my code yet, so figured I'd ask here, in case the unexpected is actually expected.

The observers in question are in my TimelineSegment - the ones created in updateItems(from items: [TimelineItem]). What I'm seeing is item.updateSamples(samples) (in the sink) getting called when a new LocomotionSample has been INSERTed and assigned to other unrelated TimelineItemBase rows.

Oh, relevant other source file: TimelineItem

I'm getting console logging like:

[4AD5120B-24F6-48DB-BAF2-3525B2F49231] updateSamples() count: 37
[AECDED05-C6AE-423A-9C14-9D0CBABF2443] updateSamples() count: 46
[4AD5120B-24F6-48DB-BAF2-3525B2F49231] updateSamples() count: 38
[AECDED05-C6AE-423A-9C14-9D0CBABF2443] updateSamples() count: 46
[8739B530-9B24-4F40-AAF7-17B63FCFE8B3] updateSamples() count: 411
[8739B530-9B24-4F40-AAF7-17B63FCFE8B3] updateSamples() count: 411
[9E49FCA8-6FC5-46C3-A585-1E3617CBF784] updateSamples() count: 1016
[9E49FCA8-6FC5-46C3-A585-1E3617CBF784] updateSamples() count: 1016

Which showing that the sink() has fired on every TimelineItem, even though only one LocomotionSample was INSERTed, and assigned to one TimelineItemBase. The sample counts in the sink() fires are correct - the correct samples are there. It's just that the observers are firing for unrelated INSERTs.

I thought it might be use of .trackingConstantRegion(), but changing it to .tracking() produces the same results.

I do have some TRIGGERs that fire on AFTER UPDATE OF timelineItemId ON LocomotionSample, and then perform UPDATE TimelineItemBase. Can see these declared in Database. But to my eyes, these triggers shouldn't be causing the ValueObservations to fire. They don't mutate either the TimelineItemBase rows or LocomotionSample rows being observed.

So I'm kind of stumped.

It's possible I'm going to end up dumping this whole approach/optimisation. It's just an experiment at this stage. But figured I should at least first find out why it's not working, before throwing it in the bin 😉

Any advice much appreciated! Thanks 🙏🏼

[groue]

Hello @sobri909,

You'll better understand the behavior of this ValueObservation with its two debugging methods.

For example:

ValueObservation
    .trackingConstantRegion(item.base.samples.fetchAll)
    .print("Sample observer for item \(item.id)")

In particular, you'll know what is the tracked region, as well as the updates that trigger the observation. Coupled with SQL tracing, this is your ultimate debugging tool.

Please see the linked docs for more information, as well as the Why is ValueObservation not publishing value changes? FAQ.

[groue]

Another thread, in the same discussion, about another topic: it looks like you are creating multiple observations, one per item.

Take care that this might not scale, performance wise.

Generally speaking, the number of observations run by an application should be bounded.

In your particular case, the tracked requests, item.base.samples, do NOT define tracked database regions that precisely target one particular item (see the beginning of the ValueObservation Performance chapter for a description of what is tracked, exactly).

You will see those tracked regions when you call observation.print.

Very likely, all those observations share the same tracked region, and are all triggered together. This does not match the precise observation firing you might expect.

We're dealing with SQLite limitations, here - GRDB can't be more precise 🤷‍♂️

Anyway. Maybe you'd better run one single observation for all items, and perform your own dispatching, item by item, at the application level.

[sobri909]

Ah, I should've known you'd have handy debug methods! Nice!

I'm seeing:

tracked region: LocomotionSample(altitude,classifiedActivityType,confirmedActivityType,course,date,horizontalAccuracy,id,latitude,longitude,movingState,recordingState,secondsFromGMT,source,speed,stepHz,timelineItemId,verticalAccuracy,xyAcceleration,zAcceleration),PlaceRTree(id,latMax,latMin,lonMax,lonMin),PlaceRTree_node(data,nodeno),PlaceRTree_parent(nodeno,parentnode),PlaceRTree_rowid(nodeno,rowid)

The same for all of them. So looks like they're all firing on the whole table as a region?

Very likely, all those observations share the same tracked region, and are all triggered together. This does not match the precise observation firing you might expect.

Sounds like it!

Take care that this might not scale, performance wise.

Yeah, I was kind of expecting to run into that. I haven't settled on the Correct or Best way to do this particular optimisation yet, and this particular approach already came with a few red flags. But I figured I should still try, even if just to eliminate it conclusively.

With new LocomotionSamples being created potentially every 2-6 seconds for many hours of the day, I have to step super carefully. The optimisations I went for in old LocoKit were great for energy efficiency, but created many nightmares for my sanity. This time I'm hoping to do better 😏

In old LocoKit I implemented TimelineSegment as a TransactionObserver. I haven't fiddled with that in years. Would that have the same limitations in terms of region tracking? Though at least I could be more precise in when I choose to fire new fetches on changes.

[sobri909]

I think I've answered my own question about TransactionObserver, by reading the docs. heh

So yeah, I'd be able to filter by table, and insert/delete/update, but beyond that I'm guessing SQLite doesn't offer us any more toys?

[groue]

Yes, that's it. TransactionObserver.databaseDidChange(with:), that SQLite calls on every individual delete/update/insert, does not get much information. Nothing more than what the DatabaseRegion type can encapsulate.

[groue]

The extra Support for SQLite Pre-Update Hooks provides more information, but I'm not sure you will be able to find the item linked to a sample from this callback.

I really suggest you perform one single observation for all items, and dispatch from there.

[sobri909]

Hmm. Thanks! Well, at least I can confidently end this optimisation experiment, and time to move on to the next one 😂

Oh, wait, can a region be observing just a specific column of a table? I could play with triggers to update isStale column values at appropriate times. And then... oh but presumably that'd still fire on the whole table, not just the specific changed row? Hmm. So all of the observers would still fire. Not a win. Time to go back to the drawing board and think up some new crazy schemes 😬

[sobri909]

I really suggest you perform one single observation for all items, and dispatch from there.

Yeah I think you're right. I could do triggers to set isStale values, then once the single observer returns results, I can walk the results and know which items to refetch samples for. Much simpler. And that involves more triggers, which always make me happy. Even though I know they're their own kind of hole digging. But it's hole digging I quite enjoy 😉

[groue]

Yes. It's not unreasonable that GRDB stops at the level of SQLite abilities, and that apps perform their extra job from there.

You should be able to share one publisher and create as many item publishers from there (with some Combine dance I'm not quite familiar with), or to create one shared observation, and again create as many item publishers from it. You'll get duplicates, of course (it item 1 is modified, item 2 will get the same samples as before), so you might need to perform some deduplication at some point.

In all cases, use ValueObservation.print or ValueObservation.handleEvents to check if the number of database accesses are under control.