Reading from the database just prior to commit in TransactionObserver

[mallman]

I want to query my database after some changes have been made in a transaction right before the transaction is committed, and I want to do this in a TransactionObserver. The databaseWillCommit method seems like the right time to do this, however the documentation for that method specifies that the method must not access the database. Further, no Database object is passed to this method.

Why does this restriction exist? Can that API be modified to pass a Database instance with the restriction that it can only be used for reading?

(Actually I have been reading from the database using my DatabasePool in TransactionObserver.databaseWillCommit, but that is now broken for reasons I won't go into here (due to a change in my app, not due to a change in GRDB). Clearly, I need to find a solution that doesn't misuse an API or I'm going to be playing "broken API whack-a-mole".)

My question derives from a particular use case in my app. In my app, I need to compute a diff to feed an NSCollectionView.performBatchUpdates(). I cannot assume that I've actually loaded every item at every path into the data source affected by a transaction. Therefore, I use my database as the source of truth for computing item indices, not the data source. To compute the diff for NSCollectionView.performBatchUpdates() I need to know the index of every item inserted, removed or updated by a transaction that affects the contents of my collection view. I can get the indexes of the inserted and updated items after the transaction is committed. Clearly I cannot get the indexes of removed items after the transaction is committed. I need to get these after the transaction changes are made, but before the changes are committed. So if you have an idea for how I might accomplish this with GRDB with the current API, I would welcome a suggestion. 😄

Basically, I want to emulate something like the following:

try dbPool.write { db in
  // blah blah change some stuff

  let sqlRequest: SQLRequest<Row> = ...
  // This is the last request in this transaction
  let results = try Row.fetchAll(db, sqlRequest)
  // do something with `results`
}

But I need to run that query at the end of any and every transaction that modifies the DatabaseRegion feeding my query.

To put it more abstractly, I want something like

try dbPool.write { db in
  // blah blah whatever

  try databaseReadCallback(db)
}

where databaseReadCallback is something that's defined by my transaction observer. Or, let's say, it's something like a TransactionObserver.databaseWillWillCommit(_ db: Database) throws method.

Cheers.

[groue]

Hello @mallman,

The databaseWillCommit callback is triggered by sqlite3_commit_hook (aka the "commit notification callback" or "commit hook"). Its documentation says:

The commit and rollback hook callbacks are not reentrant. The callback implementation must not do anything that will modify the database connection that invoked the callback. Any actions to modify the database connection must be deferred until after the completion of the sqlite3_step() call that triggered the commit or rollback hook in the first place. Note that running any other SQL statements, including SELECT statements, or merely calling sqlite3_prepare_v2() and sqlite3_step() will modify the database connections for the meaning of "modify" in this paragraph.

That's why TransactionObserver.databaseWillCommit has no Database argument, and you can not fetch anything from this callback.

And GRDB has no API for registering blocks of code to execute before all COMMIT statements.

---

But I need to run that query at the end of any and every transaction that modifies the DatabaseRegion feeding my query

As described, your use case is addressed by DatabaseRegionObservation.

It's easier to use than a raw TransactionObserver, especially when an app is not interested in individual TransactionObserver.databaseDidChange(with:) callbacks.

DatabaseRegionObservation never coalesces change notifications: you won't miss any single transaction.

DatabaseRegionObservation calls your application right after changes have been committed in the database, and before any other thread had any opportunity to perform further changes (assuming the database is only accessed via one DatabaseQueue or DatabasePool instance).

NB: If you only care about not eventually missing any change, and coalescing is acceptable, maybe consider using ValueObservation instead. ValueObservation is perfectly suitable for animating collection views, because animating directly from state A to state C is not much different that animating from A to B and then from B to C - at least from the point of view of the user.

[mallman]

I made a mistake in diagnosing the problem in my app. The logic I described in my question is actually not what I'm trying to accomplish, and it's confusing matters. I'm just going to delete this discussion unless you object. I will start another one if I have a question on what I'm actually trying to achieve. This is related to #1400, but I don't want to get into that here. I'll keep the conversation about that issue in that issue.

[groue]

Please don't delete the discussion. The question is well formed, even if it does not apply to your app.