Postgres explain impl: `Json<[Explain; 1]>` (as SQL type `JSONB`) is not compatible with SQL type `TEXT

[Kruptein]

Hi, I'm sure this is probably some configuration error on my end, but am not sure what to do.

I have my project compiling perfectly on my local machine with the compile-time query checks, but when I deploy it to my server I start getting these errors:

error: error occurred while decoding column 0: mismatched types; Rust type `sqlx_core::types::json::Json<[sqlx_core::postgres::connection::describe::Explain; 1]>` (as SQL type `JSONB`) is not compatible with SQL type `TEXT`
  --> src/handlers/users.rs:22:5
   |
22 | /     sqlx::query_as!(
23 | |         User,
24 | |         "INSERT INTO users (email, auth0_subject, created_at) VALUES ($1, $2, $3) RETURNING *",
25 | |         user.email.as_str(),
26 | |         user.sub.as_str(),
27 | |         chrono::Local::now().naive_local()
28 | |     )
   | |_____^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info

I checked the schema of the users table in both my local and server db but they both just look like:

theblackrose=# SELECT column_name, data_type FROM information_schema.columns WHERE TABLE_NAME = 'users';
  column_name  |          data_type
---------------+-----------------------------
 id            | integer
 auth0_subject | text
 email         | text
 created_at    | timestamp without time zone
(4 rows)

Can someone point me in a direction to what I might be missing ?

[mehcode]

What SQLx version? What postgres version?

[Kruptein]

Hm postgres is 10.15 on my local machine and 9.6.20 on the server, I'll try to see if I can upgrade it. Sqlx is 0.5.1

[Kruptein]

I just upgraded my server from stretch to buster and along with it postgres from 9 to 11.10, but sadly the same error is shown

[mehcode]

Can you share the User struct? From the error it looks like it might be thinking a column needs to be decoded into JSONB.

[Kruptein]

Yeah sure it's

#[derive(Debug, Deserialize, Serialize)]
pub struct User {
    pub id: i32,
    pub auth0_subject: String,
    pub email: String,
    pub created_at: chrono::NaiveDateTime,
}

edit: Removing all derives gives the same errors, adding #[derive(sqlx::Type)] also does not fix it

[Kruptein]

So far haven't been able to find a solution sadly :(

[mehcode]

error: error occurred while decoding column 0: mismatched types; Rust type sqlx_core::types::json::Json<[sqlx_core::postgres::connection::describe::Explain; 1]> (as SQL type JSONB) is not compatible with SQL type TEXT

I'm thinking this has to do with the EXPLAIN stuff added in 0.5. I'm not sure why the error is coming up only when compiling for production.

Here is the code in question:

https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/postgres/connection/describe.rs#L333

cc @abonander

[Kruptein]

Just to be clear, in both environments (my local computer and my server), I'm just running it with cargo build/run without any extra flags, so I'm not doing a special production build on the server actually

[mehcode]

As a work-around, I recommend using the offline mode support.

On your local computer:

cargo sqlx prepare

Commit the sqlx-data.json file, then try compiling on your server with SQLX_OFFLINE=1 set in the environment. It shouldn't try to introspect the queries and avoid the problem area.

[Kruptein]

ok cool, thanks that's a fine workaround for me

[abonander]

The issue is likely that the EXPLAIN (VERBOSE TRUE, FORMAT JSON) <query> statement that's executed for the column nullability detection says it's returning a TEXT column and not a JSONB column, and thus the Json adapter thinks something is wrong.

I'd be willing to bet that this was considered a bug in Postgres 9.6 and was fixed in 10.

We can fix the issue here: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/postgres/connection/describe.rs#L442

Instead of using query_as, we could do conn.query(...) and use .get_unchecked() to decode the column without checking the type. In text mode, the Json adapter should be able to parse the JSON just fine. The performance should be about the same, and skips using the prepared statement which is usually wasted effort for these one-off EXPLAINs anyway.