Exposed tries to create the same foreign key constraint even if one already exists

[kirillkuvaldin]

Hi all,

Just updated exposed to 0.22.1 from 0.17.7 and am facing this issue. The following tables player and session haven't been changed since the update.

object PlayerTable: Table() {
    val id = long("id").autoIncrement().primaryKey()
    val username = text("username").uniqueIndex().nullable()
    // omitted ...
}

object SessionTable: Table() {
    val id = long("id").primaryKey()
    val playerId = long("player_id").references(PlayerTable.id)
    // omitted ...
}

When trying to call SchemaUtils.createMissingTablesAndColumns (which supposedly shouldn't have any affect since the tables haven't changed) – I'm getting the following error

2020-03-22 09:55:40.372 [main] DEBUG Exposed - ALTER TABLE "session" ADD CONSTRAINT fk_session_player_id_id FOREIGN KEY (player_id) REFERENCES player(id) ON DELETE RESTRICT ON UPDATE RESTRICT
2020-03-22 09:55:40.373 [main] WARN  Exposed - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERROR: constraint "fk_session_player_id_id" for relation "session" already exists. Statement(s): ALTER TABLE "session" ADD CONSTRAINT fk_session_player_id_id FOREIGN KEY (player_id) REFERENCES player(id) ON DELETE RESTRICT ON UPDATE RESTRICT
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: constraint "fk_session_player_id_id" for relation "session" already exists
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:63)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:126)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:112)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:88)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:79)
	at org.jetbrains.exposed.sql.SchemaUtils.execStatements(SchemaUtils.kt:175)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:244)

For some reason it attempts to create the same foreign key for the table session fk_session_player_id_id, which it already has

                           Table "public.session"
   Column    |            Type             | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
 id          | bigint                      |           | not null |
 player_id   | bigint                      |           | not null |
 player      | jsonb                       |           | not null |
...
Indexes:
    "session_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_session_player_id_id" FOREIGN KEY (player_id) REFERENCES player(id) ON UPDATE RESTRICT ON DELETE RESTRICT

What would be the solution?

Thanks

[Tapac]

Thank you for the report. Fixed in master and will be released soon in 0.23.1

[Auronake]

Hi !

I experiment the same type of error with Exposed 0.25.1 when I use createMissingTablesAndColumns

Example Tables :

object UsersTable: IntIdTable()
and
object SpacesTable: IntIdTable() { val userId = reference("userId", UsersTable) }

Then the log :

[main] INFO Exposed - Preparing create tables statements took 26ms
[main] INFO Exposed - Executing create tables statements took 8ms
[main] INFO Exposed - Extracting table columns took 66ms
[main] INFO Exposed - Extracting column constraints took 18ms
[main] INFO Exposed - Preparing alter table statements took 94ms
SQL: ALTER TABLE spaces ADD CONSTRAINT fk_spaces_userid_id FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
[main] WARN Exposed - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERREUR: la contrainte « fk_spaces_userid_id » de la relation « spaces » existe déjà. Statement(s): ALTER TABLE spaces ADD CONSTRAINT fk_spaces_userid_id FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERREUR: la contrainte « fk_spaces_userid_id » de la relation « spaces » existe déjà
SQL: [ALTER TABLE spaces ADD CONSTRAINT fk_spaces_userid_id FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE RESTRICT ON UPDATE RESTRICT]
at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:63)
at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:126)
at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:112)
at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:88)
at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:79)
at org.jetbrains.exposed.sql.SchemaUtils.execStatements(SchemaUtils.kt:174)
at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:243)
at repository.database.DatabaseRepository$createTableAndColumn$1.invoke(DatabaseRepository.kt:39)
at repository.database.DatabaseRepository$createTableAndColumn$1.invoke(DatabaseRepository.kt:19)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:170)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:211)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:219)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:210)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:148)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:219)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:120)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:118)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:117)
at repository.database.DatabaseRepository.createTableAndColumn(DatabaseRepository.kt:37)
at repository.database.DatabaseRepository.createAndUpdate(DatabaseRepository.kt:33)
at domain.database.PrepareDatabaseUseCase.performNow(PrepareDatabaseUseCase.kt:18)
at app.MainController.(MainController.kt:20)
at app.MainControllerKt.main(MainController.kt:12)
Caused by: org.postgresql.util.PSQLException: ERREUR: la contrainte « fk_spaces_userid_id » de la relation « spaces » existe déjà
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:125)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.executeUpdate(JdbcPreparedStatementImpl.kt:23)
at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:93)
at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:61)
... 22 more

This log repeat itself 3 or 4 times

Any idea ?

[OlegZavrazhin]

@Auronake I have the same.
@Tapac Why issue has been closed?

[kkalisz]

Same for me, issue still exists in 0.41.1

[toufeeqahamedns]

Still the issue persists on 0.45