diesel_cli: joinable vanishes when a partition is added in a migration even on a fresh database (no redo)

[najtin]

Setup

Versions

• Rust: 1.60.0
• Diesel CLI: 2.0.0-rc.0
• Diesel: not needed
• Database: postgres 14.3 (docker)
• Operating System: Manjaro

Problem Description

Adding a new partition to a partitioned table in a migration causes diesel to stop generating the joinable macros. Note that the joinables where created before the new migration was added.

What are you trying to accomplish?

I just wanted to get partitions to work with Diesel. As stated in https://github.com/diesel-rs/diesel/issues/2817 adding partitions will result in an empty schema.rs on diesel_cli 1.4.1, so i updated to diesel_cli 2.0.0-rc.0. I also opened another issue with a similar but not the same problem https://github.com/diesel-rs/diesel/issues/3200 It might be that these are closely related.

What is the expected output?

The expected output is a schema.rs with joinables even after a partition is added.

What is the actual output?

Some joinables are missing in the output.

Are you seeing any additional errors?

No

Steps to reproduce

This is the project before any migrations: https://github.com/najtin/diesel_add_partition/tree/d853e16196b427277c4064ff7bf5ac5bb693f7a5 Here you can see the differences in the schema.rs https://github.com/najtin/diesel_add_partition/commit/9fd28ff02cf0514e7edc132c3827d8f902f25a2f#diff-cfb402317f9a1267396cd28c72ab83f4236dca6ebbf2f87ed850f2b82da5862dL27-L29

git clone https://github.com/najtin/diesel_add_partition
git checkout d853e16196b427277c4064ff7bf5ac5bb693f7a5
# change .env to your needs
nano .env
diesel migration run
# take a look at the joinables from schema.rs
less src/schema.rs
# start over with a fresh database or run diesel migration revert
mkdir migrations/2022-06-10-100936_add_partition_tables/
echo "drop table if EXISTS course_user_partiton_22_ss CASCADE;" > migrations/2022-06-10-100936_add_partition_tables/down.sql
echo "CREATE TABLE course_user_partiton_22_ss PARTITION OF course_user FOR VALUES FROM (2210000000000000000) TO (2220000000000000000);" > migrations/2022-06-10-100936_add_partition_tables/up.sql
diesel migration run
# notice the missing joinables
less src/schema.rs

Checklist

• [x] I have already looked over the issue tracker and the disussion forum for similar possible closed issues.

• [x] This issue can be reproduced on Rust's stable channel. (Your issue will be closed if this is not the case)
• [x] This issue can be reproduced without requiring a third party crate

Thank you very much for this awesome crate!

[weiznich]

Thanks for opening this bug report.

Diesel just queries the information_schema provided by postgresql to receive the foreign key information from there. If these tables do not contain the relevant information diesel cannot know about them. Can you please provide the result of the following query + the content of all involved tables?

https://github.com/diesel-rs/diesel/blob/b51e6584ac4c6dc4495010e6e2f282c6de327e1f/diesel_cli/src/infer_schema_internals/information_schema.rs#L328-L384

[najtin]

I replaced the main method in the diesel_cli at the commit you referenced with the following:

fn main() {
    use dotenvy::dotenv;
    dotenv().ok();
    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let constraints = infer_schema_internals::load_foreign_key_constraints(&database_url, None).unwrap();
    println!("{:?}", constraints);
}

This is the output of the query. I added some spaces and newlines for readability.

[
ForeignKeyConstraint {
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None
    },
    parent_table: TableName { 
        sql_name: "course", 
        rust_name: "course", 
        schema: None
    }, 
    foreign_key: "course_id", 
    foreign_key_rust_name: "course_id", 
    primary_key: "id" 
}, 
ForeignKeyConstraint { 
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None 
    }, 
    parent_table: TableName { 
        sql_name: "course", 
        rust_name: "course", 
        schema: None 
    }, 
    foreign_key: "course_id", 
    foreign_key_rust_name: "course_id", 
    primary_key: "id" 
}, 
ForeignKeyConstraint { 
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None 
    }, 
    parent_table: TableName { 
         sql_name: "course", 
         rust_name: "course", 
         schema: None 
    }, 
    foreign_key: "course_id", 
    foreign_key_rust_name: "course_id", 
    primary_key: "id" 
}, 
ForeignKeyConstraint { 
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None 
    }, 
    parent_table: TableName { 
        sql_name: "course", 
        rust_name: "course", 
        schema: None 
    }, 
    foreign_key: "course_id", 
    foreign_key_rust_name: "course_id", 
    primary_key: "id" 
}, 
ForeignKeyConstraint { 
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None 
    }, 
    parent_table: TableName { 
        sql_name: "user", 
        rust_name: "user", 
        schema: None 
    }, 
    foreign_key: "member_user_id", 
    foreign_key_rust_name: "member_user_id", 
    primary_key: "id" 
}, 
ForeignKeyConstraint { 
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None 
    }, 
    parent_table: TableName { 
        sql_name: "user", 
        rust_name: "user", 
        schema: None 
    }, 
    foreign_key: "member_user_id", 
    foreign_key_rust_name: "member_user_id",
    primary_key: "id" 
}, 
ForeignKeyConstraint { 
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None 
    }, 
    parent_table: TableName { 
        sql_name: "user", 
        rust_name: "user", 
        schema: None 
    }, 
    foreign_key: "member_user_id", 
    foreign_key_rust_name: "member_user_id", 
    primary_key: "id" 
}, 
ForeignKeyConstraint { 
    child_table: TableName { 
        sql_name: "course_user", 
        rust_name: "course_user", 
        schema: None 
    }, 
    parent_table: TableName { 
        sql_name: "user", 
        rust_name: "user", 
        schema: None 
    }, 
    foreign_key: "member_user_id", 
    foreign_key_rust_name: "member_user_id", 
    primary_key: "id" 
}]

All involved tables are empty.

diesel_temp=# select * from course;
 id | name 
----+------
(0 Zeilen)

diesel_temp=# select * from "user";
 id | last_name | common_name | last_activity_year 
----+-----------+-------------+--------------------
(0 Zeilen)

diesel_temp=# select * from course_user;
 course_id | member_user_id | role 
-----------+----------------+------
(0 Zeilen)

I am not sure if this is desired but it seems that query produces six duplicates and only two unique constraints.

The constraints are declared here: https://github.com/najtin/diesel_add_partition/blob/main/migrations/2022-05-28-110046_initial_setup/up.sql#L25-L26

[weiznich]

Thanks for adding this information. After inspecting a bit more what's in the corresponding postgres tables locally I think this boils down to that we just don't support partitions correctly yet. The referential_constraints contains several entries for the same foreign key. Likely the join in the first referenced query is not correct for such cases, but I do not see an obvious easy solution there. This causes the foreign key to appear multiple times in the result, which in turn causes diesel cli to discard the key as we don't support multiple foreign keys between two tables via joinable!. We cannot remove the duplicates there, as we wouldn't be able to tell if that's caused by a legitimate problem with more than one key or just by this duplication on postgres side.