postgresql 带有 golang 准备好的语句的原始 sql 事务

Raw sql transactions with golang prepared statements

提问by 1N5818

I'm having trouble finding some examples that do three of the following things:

我无法找到一些执行以下三件事的示例：

1) Allow raw sql transactions in golang.

1) 允许在 golang 中进行原始 sql 事务。

2) Use prepared statements.

2) 使用准备好的语句。

3) Rollback on query failures.

3) 查询失败回滚。

I would like to do something like this, but with prepared statements.

我想做这样的事情，但有准备好的陈述。

    stmt, stmt_err := db.Prepare(`
            BEGIN TRANSACTION;

            -- Insert record into first table.

            INSERT INTO table_1 (
                    thing_1,
                    whatever)
            VALUES(,);

            -- Inert record into second table.

            INSERT INTO table_2 (
                    thing_2,
                    whatever)
            VALUES(,);

            END TRANSACTION;
            `)
    if stmt_err != nil {
            return stmt_err
    }   
    res, res_err := stmt.Exec(
            thing_1,
            whatever,
            thing_2,
            whatever)

When I run this, I get this error: pq: cannot insert multiple commands into a prepared statement

当我运行这个时，我收到这个错误： pq: cannot insert multiple commands into a prepared statement

What gives? Are ACID compliant transactions even possible in golang? I cannot find an example.

是什么赋予了？在 golang 中甚至可以进行符合 ACID 的交易吗？我找不到一个例子。

EDIT no examples here.

编辑这里没有例子。

回答by Yandry Pozo

Yes Go has a great implementation of sql transactions. We start the transaction with db.Beginand we can end it with tx.Commitif everything goes good or with tx.Rollbackin case of error.

Yes Go 有很好的 sql事务实现。我们用db.Begin开始事务，如果一切顺利，我们可以用tx.Commit结束它，或者在出现错误时用tx.Rollback结束它。

type Tx struct { }

Tx is an in-progress database transaction.

A transaction must end with a call to Commit or Rollback.

After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.

The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback.

类型 Tx 结构 { }

Tx 是一个进行中的数据库事务。

事务必须以调用 Commit 或 Rollback 结束。

调用 Commit 或 Rollback 后，事务上的所有操作都失败并显示 ErrTxDone。

通过调用事务的 Prepare 或 Stmt 方法为事务准备的语句通过调用 Commit 或 Rollback 关闭。

Also note that we prepare queries with the transaction variable tx.Prepare(...)

另请注意，我们使用事务变量 tx.Prepare(...) 准备查询

Your function may looks like this:

您的函数可能如下所示：

func doubleInsert(db *sql.DB) error {

    tx, err := db.Begin()
    if err != nil {
        return err
    }

    {
        stmt, err := tx.Prepare(`INSERT INTO table_1 (thing_1, whatever)
                     VALUES(,);`)
        if err != nil {
            tx.Rollback()
            return err
        }
        defer stmt.Close()

        if _, err := stmt.Exec(thing_1, whatever); err != nil {
            tx.Rollback() // return an error too, we may want to wrap them
            return err
        }
    }

    {
        stmt, err := tx.Prepare(`INSERT INTO table_2 (thing_2, whatever)
                     VALUES(, );`)
        if err != nil {
            tx.Rollback()
            return err
        }
        defer stmt.Close()

        if _, err := stmt.Exec(thing_2, whatever); err != nil {
            tx.Rollback() // return an error too, we may want to wrap them
            return err
        }
    }

    return tx.Commit()
}

I have a full example here

我这里有一个完整的例子

回答by BARJ

I came up with a possible solution to rollback on any failure without any significant drawbacks. I am pretty new to Golang though, I could be wrong.

我想出了一个可能的解决方案来回滚任何失败而没有任何重大缺点。不过，我对 Golang 还是很陌生，我可能是错的。

func CloseTransaction(tx *sql.Tx, commit *bool) {
  if *commit {
    log.Println("Commit sql transaction")
    if err := tx.Commit(); err != nil {
      log.Panic(err)
    }
  } else {
    log.Println("Rollback sql transcation")
    if err := tx.Rollback(); err != nil {
      log.Panic(err)
    }
  }
}

func MultipleSqlQuriesWithTx(db *sql.DB, .. /* some parameter(s) */)  (.. .. /* some named return parameter(s) */, err error) {
  tx, err := db.Begin()
  if err != nil {
    return
  }
  commitTx := false
  defer CloseTransaction(tx, &commitTx)

  // First sql query
  stmt, err := tx.Prepare(..) // some raw sql
  if err != nil {
    return
  }
  defer stmt.Close()

  res, err := stmt.Exec(..) // some var args
  if err != nil {
    return
  }

  // Second sql query
  stmt, err := tx.Prepare(..) // some raw sql
  if err != nil {
    return
  }
  defer stmt.Close()

  res, err := stmt.Exec(..) // some var args
  if err != nil {
    return
  }

  /*
    more tx sql statements and queries here
  */

  // success, commit and return result
  commitTx = true
  return
}