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

本小节将展示如何使用Rust postgres驱动进行编程交互。用户需要时间安装、配置CockroachDB。

使用Rust postgres驱动

Step 1: 安装Rust postgres驱动

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

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: 创建表

指定maxroach用户启动内置SQL客户端,执行SQL语句,创建account表。

cockroach sql --insecure --database=bank --user=maxroach -e 'CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)'

Step 6: 编写、执行Rust代码

基本操作

Rust代码:

extern crate postgres;
use postgres::{Connection, TlsMode};
fn main() {
    let conn = Connection::connect("postgresql://maxroach@localhost:26257/bank", TlsMode::None)
        .unwrap();
    // Insert two rows into the "accounts" table.
    conn.execute(
        "INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)",
        &[],
    ).unwrap();
    // Print out the balances.
    println!("Initial balances:");
    for row in &conn.query("SELECT id, balance FROM accounts", &[]).unwrap() {
        let id: i64 = row.get(0);
        let balance: i64 = row.get(1);
        println!("{} {}", id, balance);
    }
}

创建事务(带重试逻辑)

Rust代码:

extern crate postgres;
use postgres::{Connection, TlsMode, Result};
use postgres::transaction::Transaction;
use self::postgres::error::T_R_SERIALIZATION_FAILURE;
/// Runs op 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.
fn execute_txn<T, F>(conn: &Connection, mut op: F) -> Result<T>
where
    F: FnMut(&Transaction) -> Result<T>,
{
    let txn = conn.transaction()?;
    loop {
        let sp = txn.savepoint("cockroach_restart")?;
        match op(&sp).and_then(|t| sp.commit().map(|_| t)) {
            Err(ref err) if err.as_db()
                               .map(|e| e.code == T_R_SERIALIZATION_FAILURE)
                               .unwrap_or(false) => {},
            r => break r,
        }
    }.and_then(|t| txn.commit().map(|_| t))
}
fn transfer_funds(txn: &Transaction, from: i64, to: i64, amount: i64) -> Result<()> {
    // Read the balance.
    let from_balance: i64 = txn.query("SELECT balance FROM accounts WHERE id = $1", &[&from])?
        .get(0)
        .get(0);
    assert!(from_balance >= amount);
    // Perform the transfer.
    txn.execute(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
        &[&amount, &from],
    )?;
    txn.execute(
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        &[&amount, &to],
    )?;
    Ok(())
}
fn main() {
    let conn = Connection::connect("postgresql://maxroach@localhost:26257/bank", TlsMode::None)
        .unwrap();
    // Run a transfer in a transaction.
    execute_txn(&conn, |txn| transfer_funds(txn, 1, 2, 100)).unwrap();
    // Check account balances after the transaction.
    for row in &conn.query("SELECT id, balance FROM accounts", &[]).unwrap() {
        let id: i64 = row.get(0);
        let balance: i64 = row.get(1);
        println!("{} {}", id, balance);
    }
}

查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

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