pq: Incorrect "idle in transaction" on Tx.Commit()

Hi there,

I don’t know if this has been reported before or isn’t considered a bug. I searched the issues for something like it but didn’t find anything.

I encountered the following situation:

• open a transaction
• execute a query that returns rows
• forget to close the rows
• call Commit()
• repeat

The call to Commit() returns an error but the transaction actually gets committed (as can be checked via psql). The next call to Begin() then returns an error saying “pq: unexpected transaction status idle in transaction”, even though the previous tx got committed and PostgreSQL thinks there’s no idle-in-transaction connection either (ps output says “idle”, not “idle in transaction”).

Maybe a little code:

func Test(user string, age int, conn *sql.DB) (int, error) {
    tx, err := conn.Begin()
        if err != nil {
            return 0, err
    }
    rollback := false

    defer func() {
        if rollback {
            tx.Rollback()
        } else {
            err := tx.Commit()
            fmt.Println(err)
        }
    }()

    rows, err := tx.Query("INSERT INTO users(id, name, age) VALUES (DEFAULT, $1, $2) RETURNING (id)", user, age)
    if err != nil {
        rollback = true
        return 0, err
    }

    var id int
    rows.Next() // because I know there will only be one row
    err = rows.Scan(&id)
    if err != nil {
        rollback = true
        return 0, err
    }

    return id, nil
}

Call this function twice and the second call will fail even though the data from the first call reached the database correctly. If the connection really were idle in transaction the data could not be seen in the corresponding database table.

Note: I know I should have used QueryRow (and I currently do), but that’s how I stumbled over this. Inserting a rows.Close() call before returning works, too.

But it looks like pq and PostgreSQL have differing ideas about what constitutes a successful commit. Btw, the call to Commit() returns “unexpected command tag INSERT” in this case.

I believe this is a bug in pq or should at least be clarified in the documentation.