trx_minimal_example.rs

//! The table is :
//!
//! CREATE TABLE IF NOT EXISTS _background_tasks (
//!     id BIGSERIAL PRIMARY KEY NOT NULL,
//!     name VARCHAR NOT NULL,
//! )
//!
//!
//!

use sqlx::prelude::*;
use futures::executor::block_on;
use sqlx::Executor;

fn main() {
    let pool = block_on(connect());
    block_on(async move {
        insert(&pool, "first").await;
        insert(&pool, "second").await;
        transaction_default(&pool).await;
    });
}

async fn connect() -> sqlx::PgPool {
    let url = dotenv::var("DATABASE_URL").unwrap();
    sqlx::PgPool::connect(&url).await.unwrap()
}

async fn insert(conn: impl Executor<'_, Database = sqlx::Postgres>, name: &str) {
    sqlx::query("INSERT INTO _background_tasks (name) VALUES($1)")
        .bind(name)
        .execute(conn)
        .await
        .unwrap();
}

async fn transaction_default(pg_pool: &sqlx::PgPool) {
    
    let mut conn = pg_pool.acquire().await.unwrap(); 
    conn.execute("SET default_transaction_read_only = on").await.unwrap();
    let mut transaction = pg_pool.begin().await.unwrap();

    // this should fail
    let id = sqlx::query_as::<_, (i64, String,)>("SELECT id, name FROM _background_tasks FOR UPDATE SKIP LOCKED").fetch_one(&mut transaction).await;
    
    match id {
        Err(e) => {
            println!("Success");
            println!("{:?}", e)
        },
        Ok(v) => {
            println!("Failed: {}, {}", v.0, v.1);
        }
    };
    conn.execute("SET default_transaction_read_only = off").await.unwrap();
    conn.execute("TRUNCATE TABLE _background_tasks").await.unwrap();
}