Continue looking for available names when an auto-generated constraint name is unavailable

[seancolsen]

Summary

When the API receives a POST request at /tables/{table_id}/constraints/ without a name parameter, it will auto-generate a constraint name. If the name it auto-generates isn't available, it will give up, returning an error.

Reproduce

1. Add a new empty table named foo_bar_key.

2. Add a new table with imported data by pasting the following and setting the table name to foo.

   bar
   2

3. Within the foo table, disallow duplicates for the bar column.

4. Expect success.

5. Observe an error:

   Unable to update "Allow Duplicates" of column "bar". Relation with the same name already exists.

Desired behavior

Instead of giving up right away, the backend should continue looking for available names by appending suffixes such as _1, _2, etc.

[A1O]

I'd like to work on this issue.

[kgodey]

Go ahead, @A1O!

[kgodey]

@A1O are you still working on this?

[github-actions]

This issue has not been updated in 90 days and is being marked as stale.

[purujit]

@kgodey I believe this is no longer an issue - possibly due to the new version of Postgres. When I run the reproduction steps, it no longer fails and finds a unique name for the constraint. See below how it picked foo_bar_key1 for the repro steps mentioned in this bug and foo_baz_key when there was no conflict. If you agree, please assign it to me and resolve as fixed :)

mathesar=# 
SELECT
conname AS constraint_name,
conrelid::regclass AS referring_table, 
confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint;
       constraint_name        |         referring_table          | referenced_table 
------------------------------+----------------------------------+------------------
 cardinal_number_domain_check | -                                | -
 yes_or_no_check              | -                                | -
 email_check                  | -                                | -
 uri_check                    | -                                | -
 top_level_domains_pkey       | mathesar_types.top_level_domains | -
 mathesar_json_array_check    | -                                | -
 mathesar_json_object_check   | -                                | -
 patents_pkey                 | sample.patents                   | -
 Table 1_pkey                 | sample.foo_bar_key               | -
 Table 2_pkey                 | sample.foo                       | -
 foo_bar_key1                 | sample.foo                       | -
 foo_baz_key                  | sample.foo                       | -
(12 rows)

[seancolsen]

Thanks for looking into this, @purujit

I can confirm that I'm no longer able to reproduce this bug.