Migration violates unique constraint: idx_uniq_field_table_id_parent_id_name

### Describe the bug

A customer ran into this on upgrade to v49.20. Slack context [here]().

The migration with ID `v49.2024-06-27T00:00:02` threw an error with a postgres app DB:
`ERROR: duplicate key value violates unique constraint "idx_uniq_field_table_id_parent_id_name"`.

The strange thing is, the query in the migration only updates `metabase_field.is_defective_duplicate`, but somehow throws an exception where a unique constraint on `(table_id, parent_id, name)` is violated.

So I can’t see how this is possible yet. As far as I know you shouldn’t be able to have data in Postgres that violates a unique constraint (unless there's a bug in postgres itself). You can’t even add a UNIQUE constraint with the NOT VALID [option]() like you can with FOREIGN KEY constraints:

```
metabase=# ALTER TABLE metabase_field ADD CONSTRAINT idx_uniq_field_table_id_parent_id_name
UNIQUE (table_id, parent_id, name) NOT VALID;
ERROR: UNIQUE constraints cannot be marked NOT VALID
```

Here's the full stack trace:

```
2024-08-05 12:22:54,771 ERROR liquibase.changelog :: ChangeSet migrations/001_update_migrations.yaml::v49.2024-06-27T00:00:02::calherries encountered an exception.
UPDATE SUMMARY
Run: 7
Previously run: 274
Filtered out: 49
-------------------------------
Total change sets: 330
FILTERED CHANGE SETS SUMMARY
DBMS mismatch: 49
2024-08-05 12:22:54,801 ERROR metabase.core :: Metabase Initialization FAILED
liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset migrations/001_update_migrations.yaml::v49.2024-06-27T00:00:02::calherries:
Reason: liquibase.exception.DatabaseException: ERROR: duplicate key value violates unique constraint "idx_uniq_field_table_id_parent_id_name"
Detail: Key (table_id, parent_id, name)=(81, 2237, proprietary) already exists. [Failed SQL: (0) UPDATE metabase_field mf SET is_defective_duplicate = TRUE WHERE mf.id IN (
SELECT id
FROM (
SELECT
mf.id,
ROW_NUMBER() OVER (
PARTITION BY mf.table_id, mf.name, mf.parent_id
ORDER BY
CASE WHEN mf.active THEN 0 ELSE 1 END,
CASE WHEN mf.nfc_path IS NULL THEN 0 ELSE 1 END,
mf.created_at
) AS rn
FROM metabase_field mf
) x
WHERE x.rn > 1
)]
at liquibase.command.CommandScope.execute(CommandScope.java:253)
at liquibase.Liquibase.lambda$update$0(Liquibase.java:245)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.Liquibase.runInScope(Liquibase.java:1419)
at liquibase.Liquibase.update(Liquibase.java:234)
at liquibase.Liquibase.update(Liquibase.java:212)
at liquibase.Liquibase.update(Liquibase.java:194)
at metabase.db.liquibase$migrate_up_if_needed_BANG_.invokeStatic(liquibase.clj:305)
at metabase.db.liquibase$migrate_up_if_needed_BANG_.invoke(liquibase.clj:287)
at metabase.db.setup$migrate_BANG_$fn__51480.invoke(setup.clj:80)
at metabase.db.liquibase$do_with_liquibase$f_STAR___49156.invoke(liquibase.clj:139)
at metabase.db.liquibase$do_with_liquibase.invokeStatic(liquibase.clj:142)
at metabase.db.liquibase$do_with_liquibase.invoke(liquibase.clj:130)
at metabase.db.setup$migrate_BANG_.invokeStatic(setup.clj:75)
at metabase.db.setup$migrate_BANG_.doInvoke(setup.clj:56)
at clojure.lang.RestFn.invoke(RestFn.java:445)
at metabase.db.setup$run_schema_migrations_BANG_.invokeStatic(setup.clj:147)
at metabase.db.setup$run_schema_migrations_BANG_.invoke(setup.clj:141)
at metabase.db.setup$setup_db_BANG_$fn__51508$fn__51509.invoke(setup.clj:165)
at metabase.util.jvm$do_with_us_locale.invokeStatic(jvm.clj:239)
at metabase.util.jvm$do_with_us_locale.invoke(jvm.clj:225)
at metabase.db.setup$setup_db_BANG_$fn__51508.invoke(setup.clj:160)
at metabase.db.setup$setup_db_BANG_.invokeStatic(setup.clj:159)
at metabase.db.setup$setup_db_BANG_.invoke(setup.clj:153)
at metabase.db$setup_db_BANG_$fn__51528.invoke(db.clj:69)
at metabase.db$setup_db_BANG_.invokeStatic(db.clj:64)
at metabase.db$setup_db_BANG_.invoke(db.clj:55)
at metabase.core$init_BANG__STAR_.invokeStatic(core.clj:116)
at metabase.core$init_BANG__STAR_.invoke(core.clj:101)
at metabase.core$init_BANG_.invokeStatic(core.clj:159)
at metabase.core$init_BANG_.invoke(core.clj:154)
at metabase.core$start_normally.invokeStatic(core.clj:171)
at metabase.core$start_normally.invoke(core.clj:165)
at metabase.core$entrypoint.invokeStatic(core.clj:204)
at metabase.core$entrypoint.doInvoke(core.clj:198)
at clojure.lang.RestFn.invoke(RestFn.java:397)
at clojure.lang.AFn.applyToHelper(AFn.java:152)
at clojure.lang.RestFn.applyTo(RestFn.java:132)
at clojure.lang.Var.applyTo(Var.java:705)
at clojure.core$apply.invokeStatic(core.clj:667)
at clojure.core$apply.invoke(core.clj:662)
at metabase.bootstrap$_main.invokeStatic(bootstrap.clj:31)
at metabase.bootstrap$_main.doInvoke(bootstrap.clj:28)
at clojure.lang.RestFn.invoke(RestFn.java:397)
at clojure.lang.AFn.applyToHelper(AFn.java:152)
at clojure.lang.RestFn.applyTo(RestFn.java:132)
at metabase.bootstrap.main(Unknown Source)
Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset migrations/001_update_migrations.yaml::v49.2024-06-27T00:00:02::calherries:
Reason: liquibase.exception.DatabaseException: ERROR: duplicate key value violates unique constraint "idx_uniq_field_table_id_parent_id_name"
Detail: Key (table_id, parent_id, name)=(81, 2237, proprietary) already exists. [Failed SQL: (0) UPDATE metabase_field mf SET is_defective_duplicate = TRUE WHERE mf.id IN (
SELECT id
FROM (
SELECT
mf.id,
ROW_NUMBER() OVER (
PARTITION BY mf.table_id, mf.name, mf.parent_id
ORDER BY
CASE WHEN mf.active THEN 0 ELSE 1 END,
CASE WHEN mf.nfc_path IS NULL THEN 0 ELSE 1 END,
mf.created_at
) AS rn
FROM metabase_field mf
) x
WHERE x.rn > 1
)]
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:151)
at liquibase.command.core.AbstractUpdateCommandStep.lambda$run$0(AbstractUpdateCommandStep.java:110)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.command.core.AbstractUpdateCommandStep.run(AbstractUpdateCommandStep.java:108)
at liquibase.command.core.UpdateCommandStep.run(UpdateCommandStep.java:105)
at liquibase.command.CommandScope.execute(CommandScope.java:217)
... 49 more
Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset migrations/001_update_migrations.yaml::v49.2024-06-27T00:00:02::calherries:
Reason: liquibase.exception.DatabaseException: ERROR: duplicate key value violates unique constraint "idx_uniq_field_table_id_parent_id_name"
Detail: Key (table_id, parent_id, name)=(81, 2237, proprietary) already exists. [Failed SQL: (0) UPDATE metabase_field mf SET is_defective_duplicate = TRUE WHERE mf.id IN (
SELECT id
FROM (
SELECT
mf.id,
ROW_NUMBER() OVER (
PARTITION BY mf.table_id, mf.name, mf.parent_id
ORDER BY
CASE WHEN mf.active THEN 0 ELSE 1 END,
CASE WHEN mf.nfc_path IS NULL THEN 0 ELSE 1 END,
mf.created_at
) AS rn
FROM metabase_field mf
) x
WHERE x.rn > 1
)]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:797)
at liquibase.changelog.visitor.UpdateVisitor.executeAcceptedChange(UpdateVisitor.java:119)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:68)
at liquibase.changelog.ChangeLogIterator$2.lambda$run$0(ChangeLogIterator.java:133)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:122)
at liquibase.Scope.lambda$child$0(Scope.java:186)
at liquibase.Scope.child(Scope.java:195)
at liquibase.Scope.child(Scope.java:185)
at liquibase.Scope.child(Scope.java:164)
at liquibase.Scope.child(Scope.java:252)
at liquibase.Scope.child(Scope.java:256)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:89)
... 57 more
Caused by: liquibase.exception.DatabaseException: ERROR: duplicate key value violates unique constraint "idx_uniq_field_table_id_parent_id_name"
Detail: Key (table_id, parent_id, name)=(81, 2237, proprietary) already exists. [Failed SQL: (0) UPDATE metabase_field mf SET is_defective_duplicate = TRUE WHERE mf.id IN (
SELECT id
FROM (
SELECT
mf.id,
ROW_NUMBER() OVER (
PARTITION BY mf.table_id, mf.name, mf.parent_id
ORDER BY
CASE WHEN mf.active THEN 0 ELSE 1 END,
CASE WHEN mf.nfc_path IS NULL THEN 0 ELSE 1 END,
mf.created_at
) AS rn
FROM metabase_field mf
) x
WHERE x.rn > 1
)]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:470)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:179)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1291)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1273)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:755)
... 72 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "idx_uniq_field_table_id_parent_id_name"
Detail: Key (table_id, parent_id, name)=(81, 2237, proprietary) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:297)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:464)
... 77 more
2024-08-05 12:22:54,808 INFO metabase.core :: Metabase Shutting Down ...
2024-08-05 12:22:54,809 INFO metabase.server :: Shutting Down Embedded Jetty Webserver
2024-08-05 12:22:54,817 WARN db.liquibase :: ()
2024-08-05 12:22:54,818 INFO metabase.core :: Metabase Shutdown COMPLETE
```

### To Reproduce

no repro yet.

### Expected behavior

*No response*

### Logs

*No response*

### Information about your Metabase installation

```JSON
49.20
```

### Severity

low

### Additional context

*No response*

Start with migrations/001_update_migrations.yaml and the v49.2024-06-27T00:00:02 changeset, then inspect the failed UPDATE against metabase_field and PostgreSQL's idx_uniq_field_table_id_parent_id_name constraint. Reproduce the failure using the reported key (table_id, parent_id, name)=(81, 2237, proprietary) if possible; done means the v49.20 migration completes without this constraint error and the cause is covered by a regression check.

Written by the indexing model from the issue text.