TIPS: 如遇兼容性问题,可提交github issue以获取帮助:https://github.com/cockroachdb/cockroach/issues/new

本小节将展示如何使用C++ libpqxx驱动进行编程交互。用户需要实现安装、配置CockroachDB以及C++运行环境。

使用libpqxx驱动

Step 1: 安装C++ libpqxx驱动

用户可以参考官方文档进行安装。

Step 2: 启动节点

以非安全模式启动:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 编写、执行C++代码

基本操作

代码:

// Build with g++ -std=c++11 basic-sample.cpp -lpq -lpqxx

#include <cassert>
#include <functional>
#include <iostream>
#include <stdexcept>
#include <string>
#include <pqxx/pqxx>

using namespace std;

int main() {
  try {
    // Connect to the "bank" database.
    pqxx::connection c("postgresql://maxroach@localhost:26257/bank");

    pqxx::nontransaction w(c);

    // Create the "accounts" table.
    w.exec("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)");

    // Insert two rows into the "accounts" table.
    w.exec("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");

    // Print out the balances.
    cout << "Initial balances:" << endl;
    pqxx::result r = w.exec("SELECT id, balance FROM accounts");
    for (auto row : r) {
      cout << row[0].as<int>() << ' ' << row[1].as<int>() << endl;
    }

    w.commit();  // Note this doesn't doesn't do anything
                 // for a nontransaction, but is still required.
  }
  catch (const exception &e) {
    cerr << e.what() << endl;
    return 1;
  }
  cout << "Success" << endl;
  return 0;
}

创建事务(带重试逻辑)

代码:

// Build with g++ -std=c++11 txn-sample.cpp -lpq -lpqxx
#include <cassert>
#include <functional>
#include <iostream>
#include <stdexcept>
#include <string>
#include <pqxx/pqxx>
using namespace std;
void transferFunds(
    pqxx::dbtransaction *tx, int from, int to, int amount) {
  // Read the balance.
  pqxx::result r = tx->exec(
      "SELECT balance FROM accounts WHERE id = " + to_string(from));
  assert(r.size() == 1);
  int fromBalance = r[0][0].as<int>();
  if (fromBalance < amount) {
    throw domain_error("insufficient funds");
  }
  // Perform the transfer.
  tx->exec("UPDATE accounts SET balance = balance - "
      + to_string(amount) + " WHERE id = " + to_string(from));
  tx->exec("UPDATE accounts SET balance = balance + "
      + to_string(amount) + " WHERE id = " + to_string(to));
}

// ExecuteTx runs fn inside a transaction and retries it as needed.
// On non-retryable failures, the transaction is aborted and rolled
// back; on success, the transaction is committed.
//
// For more information about CockroachDB's transaction model see
// https://cockroachlabs.com/docs/transactions.html.
//
// NOTE: the supplied exec closure should not have external side
// effects beyond changes to the database.
void executeTx(
    pqxx::connection *c, function<void (pqxx::dbtransaction *tx)> fn) {
  pqxx::work tx(*c);
  while (true) {
    try {
      pqxx::subtransaction s(tx, "cockroach_restart");
      fn(&s);
      s.commit();
      break;
    } catch (const pqxx::pqxx_exception& e) {
      // Swallow "transaction restart" errors; the transaction will be retried.
      // Unfortunately libpqxx doesn't give us access to the error code, so we
      // do string matching to identify retriable errors.
      if (string(e.base().what()).find("restart transaction:") == string::npos) {
        throw;
      }
    }
  }
  tx.commit();
}
int main() {
  try {
    pqxx::connection c("postgresql://maxroach@localhost:26257/bank");
    executeTx(&c, [](pqxx::dbtransaction *tx) {
          transferFunds(tx, 1, 2, 100);
      });
  }
  catch (const exception &e) {
    cerr << e.what() << endl;
    return 1;
  }
  cout << "Success" << endl;
  return 0;
}

执行:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank

输出:

+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

NOTE: 在默认的隔离级别SERIALIZABLE下,因读写冲突导致事务执行失败,需要用户主动地重新提交事务。用户也实现通用的retry函数在事务内部重新执行事务。