When using PGX jet will swallow errors from queries if rows are returned

[ADustyOldMuffin]

Describe the bug

When using PGX and looking at this specific doc https://pkg.go.dev/github.com/jackc/pgx/v5#Conn.Query PGX will return rows if it can, but there might be an error on the rows object indicating the transaction has failed.

Inside of both NewScanContext(rows *sql.Rows) and queryToSlice the rows.Err() is never checked, and thus is swallowed.

Environment (please complete the following information):

• OS: Linux
• Database: CockroachDB
• Database driver: PGX
• Jet version v2.11.1

Code snippet
I have no easily code snippet but repro steps are

testCTE := CTE("test_cte")
stmt := WITH(
     testCTE.AS(
          myTable.INSERT(myTable.Column).VALUES(whatever).
          RETURNING(myTable.AllColumns)
     )

)(
    SELECT(myTable.AllColumns).FROM(testCTE)
)

tx := db.Begin()

var dest resultStruct

// This returns no error, and the dest has been filled (likely with nothing as there was nothing in the CTE).
// Assume an error occurred here for whatever reason, with CRDB we can get 40001 and other errors that want you to retry.
if err := stmt.Query(tx, &dest); err != nil {
  return err
}

someOtherStmt := UPDATE(something)

// This errors out with a "Transaction has been aborted" error.
someOtherStmt.Exec(tx, nil)

Expected behavior
The error originally encountered in the first query on the transaction is returned instead of or with the data so that we can properly act on the different errors returned.

[go-jet]

rows error is checked before call to NewScanContext - here or here.

40001 is cocroachdb retry error. You'll need to implement some retry mechanism.
Check cockroachdb docs for more info- https://www.cockroachlabs.com/docs/stable/transaction-retry-error-example.

[ADustyOldMuffin]

I don't believe you read the message, the error returned from QueryContext does not have the error for the query in PGX and only contains connection type issues. The actual query error is inside of the rows interface on the Err() function which is never checked by jet.

I'd be more than happy to retry 40001 but Jet never returns the error due to it eating it. You never see any error until you learn that the transaction has been aborted due to the previous query failing.

[go-jet]

Aha, I see.

According the docs rows.Err() returns an error if there is any error during iteration. Meaning it needs to be checked after rows.Next() calls. In qrm package rows.Err() is called here.

The doc you are quoting is from the pgx specific database interface, which is not currently supported by jet(support should be added in the next release).
So, I assume you are using pgx stdlib package from - github.com/jackc/pgx/v5/stdlib, but this interface correctly checks rows.Err() inside QueryContext method.