Hi there! What is the best way to remove a unique ...

# exposed

[Romain]

Hi there! What is the best way to remove a unique index on a table? I did a quick test where I remove the .uniqueIndex() on a column, but the generated SQL for the migration doesn't remove the index. Am I supposed to remove it manually?

[Chantal Loncle]

👋 @Romain Could you confirm how you're generating the migration SQL and on which database?

I just ran a test, on all databases using 1.3.1, to confirm that:
Simply removing .uniqueIndex() from a column triggers the expected ALTER/DROP index constraint statement once MigrationUtils.statementsRequiredForDatabaseMigration() is called (or MigrationUtils.generateMigrationScript()).

If this is also what you tried, but no SQL was generated, please share some more details or a reproducer, so I can help troubleshoot.

[Chantal Loncle]

To be safe, I should also probably ask how you are applying the generated SQL and/or checking for index removal?

For example, if you generate the migration SQL, then manually execute the generated SQL, then attempt to validate removal using the same Exposed migration generation/validation methods, all in the same transaction -> you might be running into stale caches that return a false negative.

Full behavior of Exposed's migration metadata caches can be found in docs, along with how to ensure accurate schema metadata checks.

[Romain]

Hey thanks for the quick response! I'm using Postgres on this project. I'm generating the migration using MigrationUtils.statementsRequiredForDatabaseMigration and then I use execInBatch, I only did a quick test this morning after my colleague brought it up, but it's very possible that I messed up.

[Chantal Loncle]

No worries, please check out the example in the docs link above, it actually uses exactly execInBatch. So if, in the test, you attempt a validation check directly after, in the same transaction, it may incorrectly show that the index was not removed. If the result is still the same after calling resetCaches(), or if a direct pg_indexes query shows the index is not being removed, then we'll need to look more into this for sure.

[Romain]

So basically, we used to have a unique index on 3 columns that we removed to add a soft delete mechanism, so this is the change that was made: romain.paste.lol/index.kt

[Romain]

I tried to redact the sensitive stuff and the irrelevant part such as created_at/updated_at etc.

[Romain]

I just looked at the logs and the last time we can a migration, this was the only statement that was generated:

["ALTER TABLE account ADD is_deleted BOOLEAN DEFAULT FALSE NOT NULL"]

nothing about the new or the old index.

[Chantal Loncle]

Thanks for confirming, this is absolutely a bug.
The index diff check is putting more weight on the index columns being equivalent than the name differing. On a side note, the diff check is not equipped to parse differences in index filter conditions, but I've been thinking of at least getting absolute diffs in (all-or-nothing), so I'll use this as an opportunity to try addressing that again too.

I'll create a new YT ticket in the morning for your issue and add it here for you to track, if you're interested.
Should be able to get this in for the upcoming end-of-month release 👍

[Romain]

Amazing! So the reason why the filter is not created, is because of the filter condition, even if the name is different?

[Chantal Loncle]

Oh no, that's a separate issue I was mentioning -> meaning currently that 2 indexes with same name + same columns would not trigger SQL if the new model's index got a filter condition. I'd just like to find a better compromise there given our metadata-parsing limitations.

In your exact case, no SQL is being generated to both drop and add the old and new indexes because it's ignoring the valid change that is the name being different. It's taking the included columns as equivalent and only checking further for uniqueness.

[Romain]

I see, thank you! In the meantime, what's the workaround? Deleting the old index manually and not use the filter condition for now?

[Chantal Loncle]

Apologies for the delay after switching. Yes, you should be able to access the dropStatement() of the table's indexes and run it manually. Then either the migration method or a manual createStatement() should generate the expected SQL, even with the filter condition.

[Romain]

Thank you! That's pretty much what we ended up doing 🙂