Written by Marvin Hsu
on May 10, 2024

SQL Optimistic Lock in Rust

@Jornada Produtora

Last time, I discussed implementing optimistic locking using PostgreSQL's raw type xmin. As a Rust lover, I attempted to replicate this in Rust. However, I encountered unexpected challenges. Let me share the changes I encountered and how I overcome the problem.

Choose a SQL Client

There are several crates to connect to SQL databases in Rust. As a .NET user, I wanted to choose a tool as powerful as EF Core. There are two famous ORM crates: Diesel and SeaORM. Initially, I wanted to choose SeaORM because of the lack of async support for Diesel. However, both ORMs are not as convenient as EF Core, perhaps because the mechanisms of Rust and C# are quite different. Finally, I chose sqlx. SeaORM is based on sqlx, and using SQL to manipulate data is more intuitive.

The First Attempt

Similar to what I did with Dotnet last time, I need to prepare a sample table:

CREATE TABLE IF NOT EXISTS test_table (
    id      UUID        PRIMARY KEY,
    value   VARCHAR
)

It's just a simple test, connection pooling doesn't matter, I'll just use PgConnection to insert data:

let id = Uuid::new_v4();

let xmin: u32 = sqlx::query_scalar(
    r#"
    INSERT INTO test_table (id, value) VALUES ($1, $2) RETURNING xmin
    "#,
)
.bind(id)
.bind("test")
.fetch_one(&mut conn)
.await
.expect("Insert row fail.");

Something went wrong, surprising!

error[E0277]: the trait bound `u32: sqlx::Decode<'_, sqlx::Postgres>` is not satisfied
   --> lab_optimistic_lock/src/lib.rs:63:10
    |
63  |         .fetch_one(&mut conn)
    |          ^^^^^^^^^ the trait `sqlx::Decode<'_, sqlx::Postgres>` is not implemented for `u32`, which is required by `for<'r> (_,): FromRow<'r, PgRow>`

This is because sqlx-postgres not support the conversion between Rust u32 and Postgres type. You can check the conversions between Rust and Postgres types here. Maybe we can use i64.

let xmin: i64 = sqlx::query_scalar(
    r#"
    INSERT INTO test_table (id, value) VALUES ($1, $2) RETURNING xmin
    "#,
)
.bind(id)
.bind("test")
.fetch_one(&mut conn)
.await
.expect("Insert row fail.");

Still getting an error:

Insert row fail.: ColumnDecode { index: "0", source: "mismatched types; Rust type `i32` (as SQL type `INT4`) is not compatible with SQL type `xid`" }

This is because the PostgreSQL driver doesn't support the conversion between Rust types and PostgreSQL Xid. I also checked Diesel, SeaORM, and rust-postgres; none of them support this conversion. That means we must try to implement it ourselves.

Implement the Conversion between Rust Type and Postgres Xid

In fact, the PostgreSQL driver of sqlx implements another conversion for PostgreSQL oid. AAccording to the documentation of Postgres, it may be a good example to follow.

First, we create a new struct to represent Xid in Rust:

pub struct Xid(pub u32);

There are three traits we need to implement for Xid. We use a wapper of u32.

Type trait

It's used to indicate the oid of type xid. As we discussed last time, oid is a value to represent the object in PostgreSQL.

impl Type<Postgres> for Xid {
    fn type_info() -> PgTypeInfo {
        PgTypeInfo::with_oid(Oid(28))
    }
}

The PgTypeInfo enum doesn't have an xid, but we can define it ourselves. The value of xid is 28,

Encode

impl Encode<'_, Postgres> for Xid {
    fn encode_by_ref(&self, buf: &mut PgArgumentBuffer) -> IsNull {
        buf.extend(&self.0.to_be_bytes());

        IsNull::No
    }
}

This tells sqlx how to convert the PostgreSQL type into a Rust struct.

Decode

impl Decode<'_, Postgres> for Xid {
    fn decode(value: PgValueRef<'_>) -> Result<Self, BoxDynError> {
        Ok(Self(match value.format() {
            PgValueFormat::Binary => BigEndian::read_u32(value.as_bytes()?),
            PgValueFormat::Text => value.as_str()?.parse()?,
        }))
    }
}

This is the reverse way of Encode.

After implementing these three traits, we can successfully get the XID of the inserted data.

In fact, sqlx-postgres provides macros to simplify this process:

#[derive(sqlx::Type)]
#[sqlx(type_name = "xid")]
pub struct Xid(pub i32);

However, it doesn't support u32 directly; it only works with other types like i32 as a workaround.

End

You can find the complete optimistic lock example here. If you have any questions or feedback, please let me know!

Top