[Postgres] Test and document support for Cockroach DB

[mehcode]

[rakshith-ravi]

Okay, I'm not someone who uses PgSQL or CockroachDB in production, but wanted to help the project in some way, so I downloaded and ran cockroachDB and tested a few queries. I tried running the compile-time checked query! macro.

So far, here's how it goes with cockroachDB.

When I run a simple SHOW TABLES; or a SELECT * FROM table WHERE col = 'value';, I get the following error:

error: unsupported comparison operator: <oid> = <int4>
  --> src/db/misc.rs:28:13
   |
28 |       let rows = query!(
   |  ________________^
29 | |         r#"
30 | |         SELECT * FROM
31 | |             meta_data
...  |
36 | |         "#,
37 | |     )
   | |_____^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

When I try to run an insert query (using a prepared statement, with the ? and all), I get this:

error: at or near "?": syntax error
  --> src/db/misc.rs:7:2
   |
7  |       query!(
   |  _____^
8  | |         r#"
9  | |         INSERT INTO
10 | |             meta_data
...  |
20 | |         version.patch
21 | |     )
   | |_____^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

I'm not sure why this error is originating, or how to fix this, but thought I'll put this out here to help test. Oh also, I ran these queries on the terminal myself and they seem to work fine.

[mehcode]

Postgres uses $1, $2, etc. for variables.

---

error: unsupported comparison operator: =

That's potentially interesting / something we could easily fix if I can pinpoint it. Did you use master or 0.3 SQLx?

[rakshith-ravi]

I used 0.3, from crates.io, specifically, 0.3.5

[RemiKalbe]

I'm also getting that, for all of my queries :

error: unsupported comparison operator: <oid> = <int4>
  --> src/db/user.rs:79:20
   |
79 |           let user = sqlx::query_as!(
   |  ____________________^
80 | |             User,
81 | |             "SELECT *
82 | |             FROM defaultdb.users
83 | |             WHERE id = $1",
84 | |             id
85 | |         )
   | |_________^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

and with sqlx cli, when doing a migration that was working on postgres, I get:

error: while executing migrations: error returned from database: at or near "EOF": syntax error: unimplemented: this syntax

I'm on sqlx 0.3

[vultix]

I'm seeing the exact same error:

error: unsupported comparison operator: <oid> = <int4>

[RemiKalbe]

Cockroach 20.2 just came out. I don't have any compile time error now, but I have a runtime one !

"error occurred while decoding column 1: mismatched types; Rust type `i8` (as SQL type `\"CHAR\"`) is not compatible with SQL type `CHAR`

[vultix]

I’m getting the same error as @RemiKalbe when trying to use Postgres enigma with cockroach. Other than that things work fine

[altanozlu]

Hi everyone i'm using cargo sqlx prepare(v0.5.3) for cockroach v21 and i get

error: error returned from database: at or near "json": syntax error
  --> src/utils/postgres.rs:53:19
   |
53 |           let res = sqlx::query!(
   |  ___________________^
54 | |             r#"select t2.*, t1.*
55 | |         from t2
56 | |                  LEFT JOIN t1 on t1.x1 = t2.x1"#
57 | |         )
   | |_________^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

error: aborting due to previous error

i didn't use any json features cargo install sqlx-cli --no-default-features --features postgres

[MDM23]

Hello!

I was playing around and got an error while inserting values into an enum column. The code works fine with Postgres, so I guess this is related to CockroachDB.

sqlx: 0.5.5 cockroach: 20.2.5

ColumnDecode { index: "1", source: "mismatched types; Rust type `i8` (as SQL type `\"CHAR\"`) is not compatible with SQL type `CHAR`" }

use sqlx::postgres::PgPoolOptions;
use sqlx::query::Query;
use sqlx::{query, Postgres};

#[derive(Clone, Copy, Debug, sqlx::Type)]
#[sqlx(rename_all = "lowercase")]
pub enum Foobar {
    One,
    Two,
    Three,
}

#[async_std::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://root:root@localhost:26257/defaultdb")
        .await
        .unwrap();

    let queries: Vec<Query<Postgres, _>> = vec![
        query("CREATE TYPE Foobar as ENUM ('one', 'two', 'three')"),
        query("CREATE TABLE tbl (foobar Foobar)"),
        query("INSERT INTO tbl (foobar) VALUES ($1)").bind(Foobar::One),
    ];

    for query in queries {
        query.execute(&pool).await.unwrap();
    }
}

[RemiKalbe]

I've updated to cockroach v21.1, and it seems to be working correctly 🙌

[dvtkrlbs]

I get an error saying error: error returned from database: at or near "json": syntax error. I am at version v21.1. Because of that I currently do not use macros feature. My sqlx version is "0.5.5". I tried to run the test suite against the local cluster and got the same error.

[altanozlu]

I get an error saying error: error returned from database: at or near "json": syntax error. I am at version v21.1. Because of that I currently do not use macros feature. My sqlx version is "0.5.5". I tried to run the test suite against the local cluster and got the same error.

you can use https://github.com/launchbadge/sqlx/pull/1248

[dvtkrlbs]

I get an error saying error: error returned from database: at or near "json": syntax error. I am at version v21.1. Because of that I currently do not use macros feature. My sqlx version is "0.5.5". I tried to run the test suite against the local cluster and got the same error.

you can use #1248

Thank you very much will try your fork for now. I hope it gets merged quickly.

[Tarang]

One more while running migrations:

unknown function: pg_advisory_lock() which is from .lock() which is used to lock the database prior to migrations. Have not encountered any other issues other than INTEGER meaning i32 on Postgres and i64 on CockroachDB when creating tables.

[CathalMullan]

Just to update, locking during migrations can be configured now, thanks to https://github.com/launchbadge/sqlx/pull/2063

[Defman]

Hi

Is there an update on this?

I have created the following workaround, however I'm worried about nested transaction. Furthermore you lose the ability to work with PgPool and can only use the Transaction struct.

#[tokio::test]
async fn cockroachdb_test() -> anyhow::Result<()> {
    let pool = PgPool::connect("postgres://root@db:26257/defaultdb?sslmode=disable").await?;

    let mut transaction = pool.begin().await?;

    sqlx::query!(
        r#"
        CREATE DATABASE test;
    "#
    )
    .execute(&mut *transaction)
    .await?;

    sqlx::query!(
        r#"
        USE test;
    "#
    )
    .execute(&mut *transaction)
    .await?;

    migrate!()
        .set_locking(false)
        .run(&mut *transaction)
        .await?;
    

    test_function(transaction);

    // Rollback
    drop(transaction);

    Ok(())
}

[Defman]

Removing the following line solves the issue for CockroachDB, however this is not an ideal solution.

Perhaps we are lucky enough that the bug have been fixed by PostgresSQL? If not a possible refactor of how the databases are created and stored for deletion could do the trick.