Invalid schema cache causes a value observation to fail

[cosmer-work]

trafficstars

A ValueObservation fails with the 'SQLite error 1: no such table: xxx' error. This only happens when the app is first launched and the database is newly created.

In the attached example migrator.hasBeenSuperseded populates the schema cache with a SchemaInfo that has zero objects, because the migration hasn't been performed yet. The ValueObservation doesn't reset the schema cache, uses the empty schema info and then fails to resolve a foreign key relationship.

Adding a call to invalidateReadOnlyConnections works around the problem.

Environment

**GRDB flavor(s): GRDB **GRDB version: 6.25.0 **Installation method: SPM **Xcode version: 15.2 **Swift version: 5.9.2 **Platform(s) running GRDB: iOS **macOS version running Xcode: 14.3.1

[cosmer-work]

import Foundation
import GRDB

struct Foo: Codable, FetchableRecord, PersistableRecord {
    var id: String

    static let bar = hasOne(Bar.self)
    var bar: QueryInterfaceRequest<Bar> {
        request(for: Self.bar)
    }
}

struct Bar: Codable, FetchableRecord, PersistableRecord {
    var fooId: String
}

func doDatabaseTest() {
    let directory = FileManager.default.temporaryDirectory
        .appendingPathComponent("DatabaseTest", isDirectory: true)

    let path = directory
        .appendingPathComponent("database.sql", isDirectory: false)

    _ = try? FileManager.default.removeItem(at: directory)
    try! FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true)

    defer {
        _ = try? FileManager.default.removeItem(at: directory)
    }

    var migrator = DatabaseMigrator()
    migrator.registerMigration("v1") { db in
        try db.create(table: "foo") { t in
            t.column("id", .text).notNull().primaryKey()
        }

        try db.create(table: "bar") { t in
            t.autoIncrementedPrimaryKey("id")
            t.column("fooId", .text).notNull().unique().references("foo", onDelete: .cascade)
        }
    }

    let pool = try! DatabasePool(path: path.path)
    try! pool.read { db in
        if try migrator.hasBeenSuperseded(db) {
            fatalError()
        }
    }

    try! migrator.migrate(pool)

    struct FooInfo: Decodable, FetchableRecord {
        var bar: Bar
    }

    // Uncomment to work around issue.
//    pool.invalidateReadOnlyConnections()

    let observation = ValueObservation.trackingConstantRegion { db in
        try Foo
            .including(optional: Foo.bar)
            .asRequest(of: FooInfo.self)
            .fetchOne(db)
    }

    let publisher = observation.publisher(in: pool, scheduling: .immediate)
    let cancellable = publisher.sink { completion in
        switch completion {
        case .finished:
            break
        case .failure(let error):
            // Fatal error: SQLite error 1: no such table: bar
            assertionFailure(error.localizedDescription)
        }
    } receiveValue: { _ in

    }

    cancellable.cancel()
}

[groue]

Thank you for the report, @cosmer-work. I'm currently in vacations and won't be able to have a close look until a few weeks. I'm happy you could find a workaround.

Do you think something is wrong in the lib? I thought DatabasePool readers were able to automatically detect stale schema caches.

[cosmer-work]

I think it's a bug in GRDB. The various read methods do detect stale schema caches but the ValueObservation doesn't. Adding a read before the value observation also prevents the error from happening.

[groue]

It's weird because well many apps start observing right after running migrations 😅 Your reproducing code will be very precious. Indeed the schema cache is not something that should be observed by GRDB users - it should "just work"!

[groue]

Thank you @cosmer-work for discovering this bug, and your reproducing code 👍 The fix will soon ship in next version.

[cosmer-work]

Thanks for the fix.

[groue]

@cosmer-work, the ship was shipped in v6.26.0. You no longer need pool.invalidateReadOnlyConnections!

[marchy]

Thanks so much for chasing this down @cosmer-work!

We were blocked on migrating recently and had to reshuffle around some work thinking we'd have to put in some extra synchronization logic to get this working again.

It was the exact problem we were facing and the fix worked flawlessly on first test 👏