`referential_key_table_name` empty for some schemas (panic on Option::unwrap on None during migration) since sea-schema 0.14.2

[Patrick-Lehner-Woven-by-Toyota]

Hi everyone, thanks so much for your great work on the entire SeaQL project ❤️

Description

This week we found an issue where sea-orm-cli generate entity fails with a panic in sea-schema's table_constraints.rs here when it tries to unwrap the Option result.referential_key_table_name that is None in our case.

This seems to be the same error behavior as described in https://github.com/SeaQL/sea-orm/discussions/2131.

Steps to Reproduce

1. Have a situation where one table T1 has a foreign key (FK) that references a column C in another table T2, where there is a unique index on C but not a unique key (which postgres lets you do, see this SO answer)
2. Run sea-orm-cli generate entity

Expected Behavior

The entities should be generated according to the schema.

If there is a problem with the schema, an informative error message should be printed.

Actual Behavior

The tool panics without any useful error message.

Reproduces How Often

Always (with our schema)

Versions

We are using postgres 14.5.

This issue occurs for us when sea-orm-cli is using [email protected] and does not occur when sea-orm-cli is using [email protected].

We first noticed this after one of our team members recently installed the latest version of sea-orm-cli (0.12.15) where this issue occurred, while on another person's machine it was still working with an older installation of [email protected]. When that person upgraded to latest, they also experienced the error.

We also noticed that when you install an older version of sea-orm-cli with only the version selector (e.g. cargo install [email protected]), Cargo will still use the newest dependencies that match the tool's dependencies spec and will not use the versions that it was published with.

Since sea-orm-cli used the dependency sea-schema = { version = "0.14.0" } (see here) up to 0.12.11, the newer patch version 0.14.2 of sea-schema still matches that version specifier and gets used.

To install an older sea-orm-cli with an older sea-schema for comparative testing, you can use the --locked switch to make Cargo use the versions used at time of publishing, e.g. cargo install --locked [email protected].

Additional Information

Here's a shell script that creates a postgres docker container, creates two tables with the unique/FK combination that causes the problem, and then runs sea-orm-cli generate entity on it to trigger the issue.

You need to have docker and [email protected] on the PATH for this to work.

https://gist.github.com/Patrick-Lehner-Woven-by-Toyota/811668880cf78a1bc78c679993ce137d

[ghost]

After investigating further, we have found what specifically causes the issue in our schema: one table T1 has a foreign key (FK) that references a column C in another table T2, where there is a unique index on C but not a unique key (which postgres lets you do, see this SO answer)

I've updated the description above and provided an MRE in the form of a shell script that creates a postgres docker container with two minimal tables, and this kind of FK relation that triggers the issue.

While investigating the problem, I found https://github.com/SeaQL/sea-schema/issues/116#issuecomment-1735085067 in a similar issue report from some months ago.

If I slightly adjust the query that sea-schema seems to run to this:

SELECT
    "table_constraints"."constraint_schema",
    "table_constraints"."constraint_name",
    "table_constraints"."table_schema",
    "table_constraints"."table_name",
    "table_constraints"."constraint_type",
    "table_constraints"."is_deferrable",
    "table_constraints"."initially_deferred",
    "check_constraints"."check_clause",
    "key_column_usage"."column_name",
    "key_column_usage"."ordinal_position",
    "key_column_usage"."position_in_unique_constraint",
    "referential_constraints_subquery"."unique_constraint_schema",
    "referential_constraints_subquery"."unique_constraint_name",
    "referential_constraints_subquery"."match_option",
    "referential_constraints_subquery"."update_rule",
    "referential_constraints_subquery"."delete_rule",
    "referential_constraints_subquery"."table_name",
    "referential_constraints_subquery"."column_name"
FROM
    "information_schema"."table_constraints"
        LEFT JOIN "information_schema"."check_constraints" ON "table_constraints"."constraint_name" = "check_constraints"."constraint_name"
        AND "table_constraints"."constraint_catalog" = "check_constraints"."constraint_catalog"
        AND "table_constraints"."constraint_schema" = "check_constraints"."constraint_schema"
        LEFT JOIN "information_schema"."key_column_usage" ON "table_constraints"."constraint_name" = "key_column_usage"."constraint_name"
        AND "table_constraints"."constraint_catalog" = "key_column_usage"."constraint_catalog"
        AND "table_constraints"."constraint_schema" = "key_column_usage"."constraint_schema"
        AND "table_constraints"."table_catalog" = "key_column_usage"."table_catalog"
        AND "table_constraints"."table_schema" = "key_column_usage"."table_schema"
        AND "table_constraints"."table_name" = "key_column_usage"."table_name"
        LEFT JOIN (
        SELECT
            DISTINCT "referential_constraints"."constraint_name",
                     "referential_constraints"."unique_constraint_schema",
                     "referential_constraints"."unique_constraint_name",
                     "referential_constraints"."match_option",
                     "referential_constraints"."update_rule",
                     "referential_constraints"."delete_rule",
                     "constraint_column_usage"."table_name",
                     "constraint_column_usage"."column_name"
        FROM
            "information_schema"."referential_constraints"
                LEFT JOIN "information_schema"."constraint_column_usage" ON "referential_constraints"."constraint_name" = "constraint_column_usage"."constraint_name"
    ) AS "referential_constraints_subquery" ON "table_constraints"."constraint_name" = "referential_constraints_subquery"."constraint_name"
WHERE
    "table_constraints"."table_schema" = 'public'
  AND constraint_type = 'FOREIGN KEY'
  AND "referential_constraints_subquery"."table_name" NOT IN (
    SELECT
        "pg_class"."relname"
    FROM
        "pg_inherits"
            JOIN "pg_class" ON "pg_inherits"."inhrelid" = "pg_class"."oid"
            AND "pg_class"."relkind" IN ('r', 't', 'v', 'm', 'f', 'p')
)
ORDER BY
    "table_constraints"."constraint_name" ASC,
    "key_column_usage"."ordinal_position" ASC,
    "referential_constraints_subquery"."unique_constraint_name" ASC,
    "referential_constraints_subquery"."constraint_name" ASC

And run it on the MRE database above (or our actual schema), we get output like this:

constraint_schema	constraint_name	table_schema	table_name	constraint_type	is_deferrable	initially_deferred	check_clause	column_name	ordinal_position	position_in_unique_constraint	unique_constraint_schema	unique_constraint_name	match_option	update_rule	delete_rule	table_name	column_name
public	table2_fk_u_fkey	public	table2	FOREIGN KEY	NO	NO	null	fk_u	1	1	null	null	NONE	NO ACTION	NO ACTION	table1	u

Notably, unique_constraint_name is NULL, whereas for other FKs that reference primary keys, those columns are not NULL.

Curiously, the last two columns, "referential_constraints_subquery"."table_name" and "referential_constraints_subquery"."column_name", are not NULL for this FK. Since the panic occurs at result.referential_key_table_name.unwrap() (here), I would have expected these columns to matter, since that field seems to be populated from them.

Please let me know what you think about this.

[ghost]

Could this be related to this recent change that was included in 0.14.2? https://github.com/SeaQL/sea-schema/pull/118

[28Smiles]

Could this be related to this recent change that was included in 0.14.2? #118

Yes it is, i encountered this issue while implementing a diffent feature. Reverting the changes from #118 fixed it for me. I dont know what is wrong or how to fix it properly tho.

[tyt2y3]

Thank you for the Docker script, sad there is a regression. But yeah we didn't have these test cases. I paste the sql here for reference @billy1624

    CREATE TABLE "table1" (
        "id" SERIAL NOT NULL PRIMARY KEY,
        "u" INT NOT NULL
    );
    CREATE UNIQUE INDEX "u_idx" ON "table1" ("u");
    CREATE TABLE "table2" (
        "fk_u" INT NOT NULL PRIMARY KEY REFERENCES "table1" ("u")
    );