go-dqlite doesn't support nested or interleaved Query

[cole-miller]

@ycliuhw pointed out (MM) that go-dqlite can't handle running a query on a Tx while in the middle of iterating over the rows from a previous query on the same Tx, thus:

    // Prepare the first query
    rows, err := tx.Query("SELECT id FROM table1")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        if err := rows.Scan(&id); err != nil {
            log.Fatal(err)
        }

        // Inside the loop, initiate a new query based on the current row's data
        innerRows, err := tx.Query("SELECT info FROM table2 WHERE related_id = ?", id)
        if err != nil {
            log.Fatal(err)
        }
        
        for innerRows.Next() {
            var info string
            if err := innerRows.Scan(&info); err != nil {
                log.Fatal(err)
            }
            fmt.Println("Info:", info)
        }
        innerRows.Close() // Make sure to close the inner cursor
    }

I think this is ultimately due to limitations in the wire protocol but need to think through it a bit more.

[freeekanayaka]

It should be possible to tweak the wire protocol (and/or its implementation) to accommodate this use case, and do it in a backward-compatible way.

However, may I ask what is the real-world situation where the above pattern is desired? Normally it's much more efficient to avoid this N * M pattern and turn it into an N + M pattern, or even better just N, e.g.:

• use a join (turning this into an N pattern)
• fully iterate the first result set, collecting the all IDs or data you need, and then issue a single (or more, if the data is not homogeneous) bulk queries collecting all the additional data at once (N + M pattern)

So while it's true that this can't currently be done in dqlite, I tend to think that in most cases (if not all) this pattern leads to less-than-optimal data access.

[tomponline]

So while it's true that this can't currently be done in dqlite, I tend to think that in most cases (if not all) this pattern leads to less-than-optimal data access.

Tend to agree too. In the past when we've hit this issue in LXD its been down to a bug or accidental nested transaction rather than a desirable scenario.

[cole-miller]

Sorry, just circling back to this now. When this was brought up on MM the person reporting it noted that yes, this can and probably should be done with a join, but also that the mattn/go-sqlite3 driver does support this kind of interleaving.

So probably the best thing to do here is to find a place to document that go-dqlite doesn't support this. Eventually we will start tagging requests and responses in the wire protocol with unique IDs (since there are other good reasons to do this) and I think that should also unlock support for this feature, but it doesn't seem like supporting said feature is a compelling reason to increase priority for that task.

[SimonRichardson]

@cole-miller I would agree this is a low priority. The statement has been reworked to no longer do this.