Value observation with generated columns

[mbrandonw]

Right now value observation does not play nicely with generated columns in SQLite. Consider a schema like this:

CREATE TABLE t(
  a INTEGER,
  b INTEGER AS (a + 1) STORED
);

Then a query like this:

SELECT b FROM t

…will not observe changes to the column a since it is not mentioned in the query directly.

This can be verified with the following test (add to ValueObservationTests):

func testValueObservationWithGeneratedColumns() throws {
    let dbQueue = try makeDatabaseQueue()
    try dbQueue.write { db in
        try db.execute(sql: """
        CREATE TABLE t(
            a INTEGER,
            b INTEGER GENERATED ALWAYS AS (a + 1) STORED
        );
        INSERT INTO t(a) VALUES (1);
        """)
    }
    
    let observation = ValueObservation.tracking { db in
        try Int.fetchOne(db, sql: "SELECT b FROM t")!
    }
    
    let notificationExpectation = expectation(description: "notification")
    notificationExpectation.expectedFulfillmentCount = 2
    let observedValuesMutex: Mutex<[Int]> = Mutex([])
    let cancellable = observation.start(
        in: dbQueue,
        scheduling: .immediate,
        onError: { error in XCTFail("Unexpected error: \(error)") },
        onChange: { value in
            observedValuesMutex.withLock { $0.append(value) }
            notificationExpectation.fulfill()
        })
    
    try withExtendedLifetime(cancellable) {
        try dbQueue.write { db in
            try db.execute(sql: "UPDATE t SET a = 2")
        }
        waitForExpectations(timeout: 2, handler: nil)
        XCTAssertEqual(observedValuesMutex.load(), [2, 3])
    }
}

Is it worth updating the behavior of value observation so that whenever a generated column is selected it forces observation of all columns?

[groue]

hello @mbrandonw,

Yes, updates to column "a" do not trigger observations of column "b". In the case of generated columns, I agree that this is not what the library user is expecting. The reason why is that GRDB performs string comparisons: "a" != "b". Because of that, the solution, if it exists, is at the GRDB level indeed. You had a good idea opening this discussion.

Is it worth updating the behavior of value observation so that whenever a generated column is selected it forces observation of all columns?

We can ask SQLite if a column is generated indeed, thanks to PRAGMA table_xinfo. And this would allow GRDB to listen to updates on all columns whenever a generated column is observed.

PRAGMA table_xinfo was introduced in SQLite 3.26.0, and generated columns in 3.31.0. There is no risk of exhibiting inconsistent behaviors depending on the operating system.

Let's consider a downside of this technique, though. If the generated column b depends on a, then updating other columns than a would trigger observations of b. This behavior would be correct (i.e. compatible with the documented behavior of ValueObservation). Still, this would be a missed optimization.

So it is worth looking for another technique.

We could keep on expanding DatabaseSchemaSource, the protocol that extends SQLite schema introspection, and add a method similar to KVO keyPathsForValuesAffectingValue(forKey:):

struct MySchemaDataSource: DatabaseSchemaSource {
    func columnsAffectingGeneratedColumn(_ column: String, inTable table: DatabaseObjectID) -> Set<String>? {
        if table.name == "t", column == "b" {
            // t.b is generated from a
            return ["a"]
        } else {
            return nil
        }
    }
}

Maybe a mix of both techniques would be the sweetest spot. When observing a generated column b:

• If columnsAffectingGeneratedColumn returns nil, observe all columns (safest bet).
• If columnsAffectingGeneratedColumn does not return nil, only observe the returned columns (optimized case).

[mbrandonw]

Hi @groue, that all sounds reasonable to me. Thanks for sharing your thoughts!

[groue]

All right, then. Consider the feature request as submitted!