Exposed attempts to create indices that already exist

[simolus3]

I ran into crashes with Exposed when creating the same table with indices across different Postgres schemas. I think this might be related to https://github.com/JetBrains/Exposed/issues/797, except that this affects indices instead of tables.

This can be reproduced with the following code and steps below:

package repro

import org.jetbrains.exposed.dao.id.LongIdTable
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.transactions.transaction

object Users : LongIdTable() {
    val firebaseId = varchar("firebase_id", 36).uniqueIndex()
    val chatId = long("chat").nullable().index()
}

fun main() {
    val db = Database.connect("jdbc:postgresl://database url here?currentSchema=someSchema")
    transaction(db) {
        SchemaUtils.createMissingTablesAndColumns(Users)
    }
}

1. Run the repro on one postgres schema
2. Create another schema in postgres
3. Change the code so that currentSchema points to the new schema
4. Run it once. As expected, this outputs:

   2020-09-01 15:43:27.735 [main] INFO  Exposed - Preparing create tables statements took 53ms
   2020-09-01 15:43:27.756 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS users (id BIGSERIAL PRIMARY KEY, firebase_id VARCHAR(36) NOT NULL, chat BIGINT NULL)
   2020-09-01 15:43:27.766 [main] DEBUG Exposed - ALTER TABLE users ADD CONSTRAINT users_firebase_id_unique UNIQUE (firebase_id)
   2020-09-01 15:43:27.774 [main] DEBUG Exposed - CREATE INDEX users_chat ON users (chat)

   However, it also outputs:

2020-09-01 15:43:28.231 [main] WARN  Exposed - Indices missed from database (will be created):
2020-09-01 15:43:28.231 [main] WARN  Exposed -      Unique Index 'users_firebase_id_unique' for 'users' on columns repro.Users.firebase_id
2020-09-01 15:43:28.231 [main] WARN  Exposed - Indices exist in database and not mapped in code on class 'users':
2020-09-01 15:43:28.231 [main] WARN  Exposed -      Unique Index 'users_firebase_id_unique' for 'users' on columns repro.Users.firebase_id, repro.Users.firebase_id

5. Run the snippet again, it now crashes because it attempts to create indices that already exist:

   2020-09-01 15:45:08.209 [main] WARN  Exposed - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERROR: relation "users_firebase_id_unique" already exists. Statement(s): ALTER TABLE users ADD CONSTRAINT users_firebase_id_unique UNIQUE (firebase_id)
   org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "users_firebase_id_unique" already exists

[gerritc]

I created a pull request (#1068) that solves the problem for me.

[MihaelBercic]

I created a pull request (#1068) that solves the problem for me.

Seems to still be happening, even after the PR merged?