joinable! not generated when FKs in different tables have the same name

[tp-woven]

Setup

Versions

• Rust: 1.69, 2021 edition
• Diesel: 2.1.0
• Database: Postgres (postgres:latest Docker image)
• Operating System: MacOS Ventura 13.4

Feature Flags

• diesel: postgres

Problem Description

Having two tables with a foreign key that has the same name causes joinable to not be generated.

What are you trying to accomplish?

This is the "first" migration in a project, and I have the following table definitions:

CREATE TABLE "kinds"
(
    "kind_id"   SERIAL  NOT NULL,
    "kind_name" VARCHAR NOT NULL UNIQUE,
    PRIMARY KEY ("kind_id")
);

CREATE TABLE "data_mapping"
(
    "data_kind_id__kind_id" INT     NOT NULL,
    "data_attribute"        VARCHAR NOT NULL,
    PRIMARY KEY ("data_attribute"),
    CONSTRAINT fk_data_kind FOREIGN KEY (data_kind_id__kind_id) REFERENCES kinds (kind_id)
);

CREATE TABLE "other_table"
(
    "data_kind_id__kind_id" INT     NOT NULL,
    "data_attribute"        VARCHAR NOT NULL,
    PRIMARY KEY ("data_attribute"),
    CONSTRAINT fk_data_kind FOREIGN KEY (data_kind_id__kind_id) REFERENCES kinds (kind_id)
);

When I run the migration, the generated schema.rs contains the table! definitions but not the joinable!.

What is the expected output?

diesel::joinable!(data_mapping -> kinds (data_kind_id__kind_id));
diesel::joinable!(other_table -> kinds (data_kind_id__kind_id));

What is the actual output?

No joinable!.

Are you seeing any additional errors?

Not an additional error, but some more information...

Changing the constraint name so that it doesn't match between the two tables does generate the joinable!. Similarly, when switching to "inline" FKs on the column definitions ("data_kind_id__kind_id" INT NOT NULL REFERENCES kinds (kind_id)) works as well, because the generated constraint names do not match.

Steps to reproduce

Fresh database, create a migration with the tables above, and apply the migration.

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

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

[weiznich]

Thanks for opening this issue. I can reproduce that issue, but I do not see a easy way to fix it. Diesel queries the postgresql information_schema for database introspection information, including information about the foreign key constraints. In detail: We use the information_schema.table_constraints table to get the foreign key information. Now the problem in this case is that this table does not contain information about that foreign key. You can verify that by running select * from information_schema.table_constraints where table_name = 'kinds'; on a database where the relevant migration is applied. Now that raises the following questions:

• Do we use the wrong table to get these information?
• Does postgres not include all information there (so is that a bug there?)

The relevant query is here:

diesel/diesel_cli/src/infer_schema_internals/pg.rs

Lines 183 to 197 in c5a9f7b

	let constraint_names = tc::table
	.filter(tc::constraint_type.eq("FOREIGN KEY"))
	.filter(tc::table_schema.eq(schema_name))
	.inner_join(
	rc::table.on(tc::constraint_schema
	.eq(rc::constraint_schema)
	.and(tc::constraint_name.eq(rc::constraint_name))),
	)
	.select((
	rc::constraint_schema,
	rc::constraint_name,
	rc::unique_constraint_schema,
	rc::unique_constraint_name,
	))
	.load::<(String, String, Option<String>, Option<String>)>(connection)?;

I'm happy to receive suggestions (or PR's) how to resolve that issue.

[tp-woven]

Thanks for looking into this!

I'm not sure what the issue is, but when I inspect the database, I do see the FK in both table_constraints and referential_constraints. One thing that sticks out in my local schema (with the other FK I mentioned in the original comment) is that constraint_name is not unique - should the join you pointed to also join on the table name? Possibly related, but does this mean that the PK for referential_constraints only consists of the schema and constraint name? (sorry, I'm a diesel newbie, so not sure what that syntax means exactly 😓)

[weiznich]

I'm not sure what the issue is, but when I inspect the database, I do see the FK in both table_constraints and referential_constraints.

Could you share the relevant entries there?

should the join you pointed to also join on the table name?

It cannot use the table name there as referential_constraints (rc in that query) does not contain the table name.

[tp-woven]

referential_constraints (rc in that query) does not contain the table name.

Oh, you're right, for some reason I thought I saw it there...

Still, looking at the view I see this:

consent=# SELECT t.*
consent-#                 FROM information_schema.referential_constraints t;
 constraint_catalog | constraint_schema | constraint_name | unique_constraint_catalog | unique_constraint_schema | unique_constraint_name | match_option | update_rule | delete_rule
--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------
 consent            | public            | fk_data_kind    | consent                   | public                   | kinds_pkey             | NONE         | NO ACTION   | NO ACTION
 consent            | public            | fk_data_kind    | consent                   | public                   | kinds_pkey             | NONE         | NO ACTION   | NO ACTION
... <2 other unrelated rows>
(4 rows)

[weiznich]

Well the problem there is that, while referential_constraints seems to contain that foreign key we cannot match it to a table (as it's missing from table_constraints). Without that we cannot generate a joinable! call.

[tp-woven]

Sorry, I think there's a misunderstanding here - the FK does show up in table_constraints:

consent=# SELECT t.* FROM information_schema.table_constraints t WHERE table_name='data_mapping';
 constraint_catalog | constraint_schema |    constraint_name    | table_catalog | table_schema |  table_name  | constraint_type | is_deferrable | initially_deferred | enforced | nulls_distinct
--------------------+-------------------+-----------------------+---------------+--------------+--------------+-----------------+---------------+--------------------+----------+----------------
 consent            | public            | data_mapping_pkey     | consent       | public       | data_mapping | PRIMARY KEY     | NO            | NO                 | YES      |
 consent            | public            | fk_data_kind          | consent       | public       | data_mapping | FOREIGN KEY     | NO            | NO                 | YES      |
 consent            | public            | 2200_33044_1_not_null | consent       | public       | data_mapping | CHECK           | NO            | NO                 | YES      |
 consent            | public            | 2200_33044_2_not_null | consent       | public       | data_mapping | CHECK           | NO            | NO                 | YES      |
(4 rows)

I have also confirmed that using the CONSTRAINT syntax but giving the foreign keys on the different tables different names does generate the joinable! statements in schema.rs. I guess switching to REFERENCES on the column definition worked because then the constraint name is auto-generated...

Edit: I've updated the issue title/description to reflect this, and also added a test that reproduces this in #3650.

[weiznich]

Fixed by #3650