[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.