2 statements in 1 transaction

[otiai10]

Question and need helps :(

I can execute sql below.

mydb=# BEGIN TRANSACTION;
BEGIN
mydb=# SELECT id, state FROM jobs FOR UPDATE;
                  id                  | state
--------------------------------------+-------
 4a6f9f45-537a-11e4-9298-3c15c2e679d8 | READY
(1 row)

mydb=# UPDATE jobs SET state = 'RUNNING' WHERE id = '4a6f9f45-537a-11e4-9298-3c15c2e679d8';
UPDATE 1
mydb=# COMMIT TRANSACTION ;
COMMIT
mydb=# SELECT id, state FROM jobs FOR UPDATE;
                  id                  |  state
--------------------------------------+---------
 4a6f9f45-537a-11e4-9298-3c15c2e679d8 | RUNNING
(1 row)

mydb=#

I want to realize the same thing of this, I wrote this code

package main

import (
    "fmt"
    "database/sql"
    _ "github.com/lib/pq"
)

func main() {

    db, _ := sql.Open("postgres", "host=localhost dbname=mydb sslmode=disable")
    defer db.Close()

    tx, _ := db.Begin()
    defer tx.Rollback()

    stmt1, _ := tx.Prepare("SELECT id FROM jobs WHERE state = 'READY' FOR UPDATE;")
    rows, _ := stmt1.Query()

    for rows.Next() {
        var id string
        rows.Scan(&id)

        stmt2, e := tx.Prepare("UPDATE jobs WHERE id = $1 SET state = 'RUNNING';")
        // it fails
        if e != nil {
            panic(e)
        }

        r, e := stmt2.Exec(id)
        fmt.Println(r, e)
    }

    tx.Commit()

    return
}

finally I got this error

panic: pq: unexpected describe rows response: 'D'

However I read #142 and #254 , I couldn't solve this problem.

Is there anyway to realize this by using lib/pq?

Thank you.

[johto]

As for the immediate problem, you'd have to read the results of your first query somewhere before running the second one. In this case a slice of strings would work fine.

But there's no need to use FOR UPDATE in the first place; just UPDATE jobs SET state = 'running' WHERE state = 'READY' RETURNING id would work and avoid the extra round-trips caused by having to run two queries in an explicit transaction.

[otiai10]

THANK YOU VERY MUCH!!

But there's no need to use FOR UPDATE in the first place; just UPDATE jobs SET state = 'running' WHERE state = 'READY' RETURNING id would work and avoid the extra round-trips caused by having to run two queries in an explicit transaction.

The problem was that I didn't understand sql well.

I'm so ashamed... and appreciate your kindness!!