Empty or incorrect unique_constraint generation

[mmalek-sa]

There seems to be a fundamental difference between PostgreSQL implementation of pg_constraint vs CockroachDB's implementation. When ActiveRecord queries this table here, Postgres returns an empty array while CockroachDB returns list of all indexes with a unique constraint.

This causes ActiveRecord to swap t.index definitions with t.unique_constraint definitions here. The problem with this is that Postgres Adapter doesn't seem to support conditions and functions for constraints properly. This results in these index definitions to change to a wrong definition when using CockroachDB:

t.index "lower(name::STRING) ASC", name: "index_redacted_table_name_on_name_unique", unique: true

becomes:

t.unique_constraint [], name: "index_redacted_table_name_on_name_unique"

and

t.index ["username"], name: "index_users_on_username_unique", , unique: true, where: "(deleted is false)"

becomes

t.unique_constraint ["username"], name: "index_users_on_username_unique"

I'm able to fix this by monkey patching SchemaStatements#unique_constraints method to return an empty array as PostgreSQL does:

def unique_constraints(_table_name)
  []
end

This is happening in Rails >= 7.1.