Inconsistent Field Order in PostgreSQL: Risks and Recommendations

[liutaon]

Bug Description

In two different environments (for example, Test and Prod environments), when the order of fields in a table is inconsistent, using select * from table can lead to misplacement of field values or, in more severe cases, a panic.

This is a very serious potential issue. It should be explicitly stated in the documentation that data must be accessed using field names, not by using select * from.

Info

• SQLx version: 0.7.3
• SQLx features enabled: "postgres", "runtime-tokio", "json", "macros", "time", "rust_decimal",
• Database server and version: PostgreSQL 15
• Operating system: MacOS, Linux
• rustc --version: rustc 1.76.0 (07dca489a 2024-02-04)

Minimal Reproduction

#[derive(Debug)]
struct User {
    id: i32,
    name: String,
    age: i32,
}

#[tokio::main]
async fn main() {
    dotenvy::dotenv().ok();
    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let pool = sqlx::PgPool::connect(&database_url).await.unwrap();
    let users = sqlx::query_as!(User, "select * from test_user")
        .fetch_all(&pool)
        .await
        .unwrap();
    println!("{:?}", users);
}

Test & Ooutput:

test=> \d test_user

 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | integer           |           | not null | 
 name   | character varying |           | not null | 
 age    | integer           |           | not null | 

[User { id: 1, name: "test", age: 19 }]

Prod & Output:

prod=> \d test_user

 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | integer           |           | not null | 
 age    | integer           |           | not null | 
 name   | character varying |           | not null | 

[User { id: 1, name: "\0\0\0\u{13}", age: 1952805748 }]

[abonander]

This is already covered in the documentation improvements added by #3007