fk constraint already exists error with createMissingTablesAndColumns

[Auronake]

Hi !

I experiment an error with Exposed 0.25.1 and Postgresql in a Javalin Project in Kotlin when I use createMissingTablesAndColumns

'exposed_core' : 'org.jetbrains.exposed:exposed-core:0.25.1',
'exposed_dao' : 'org.jetbrains.exposed:exposed-dao:0.25.1',
'exposed_jdbc' : 'org.jetbrains.exposed:exposed-jdbc:0.25.1',
'postgresql' : 'org.postgresql:postgresql:42.2.12'

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 ? Need more information ?

Thank you !

[Auronake]

I've tried with DSL annotation (for test)

object UsersTable: Table() { val id: Column<Int> = integer("id").autoIncrement() override val primaryKey = PrimaryKey(id, name = "PK_UsersTable_Id") }

object SpacesTable: Table() { val id: Column<Int> = integer("id").autoIncrement() val userId: Column<Int> = integer("userId").references(UsersTable.id, ReferenceOption.CASCADE, ReferenceOption.CASCADE) override val primaryKey = PrimaryKey(id, name = "PK_SpacesTable_Id") }

Same result :(

[Tapac]

@Auronake , do you create tables on an empty database or tables already exists?

[freedom4live]

@Tapac Managed to reproduce on master branch in CreateMissingTablesAndColumnsTests with a test:

    @Test fun testBug() {
        val usersTable = object : IntIdTable("users"){}
        val spacesTable = object : IntIdTable("spaces") { val userId = reference("userId", usersTable) }

        withDb(TestDB.POSTGRESQL) {
            SchemaUtils.createMissingTablesAndColumns(usersTable, spacesTable)
        }
    }

Result:
20:29:00,492 WARN Test worker Exposed:invoke:183 - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ОШИБКА: ограничение "fk_spaces_userid_id" для отношения "spaces" уже существует. 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: ОШИБКА: ограничение "fk_spaces_userid_id" для отношения "spaces" уже существует 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 org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:228) at org.jetbrains.exposed.sql.tests.shared.ddl.CreateMissingTablesAndColumnsTests$testBug$1.invoke(CreateMissingTablesAndColumnsTests.kt:159) at org.jetbrains.exposed.sql.tests.shared.ddl.CreateMissingTablesAndColumnsTests$testBug$1.invoke(CreateMissingTablesAndColumnsTests.kt:20) at org.jetbrains.exposed.sql.tests.DatabaseTestsBase$withTables$$inlined$forEach$lambda$1.invoke(DatabaseTestsBase.kt:141) at org.jetbrains.exposed.sql.tests.DatabaseTestsBase$withTables$$inlined$forEach$lambda$1.invoke(DatabaseTestsBase.kt:98) at org.jetbrains.exposed.sql.tests.DatabaseTestsBase$withDb$2.invoke(DatabaseTestsBase.kt:121) at org.jetbrains.exposed.sql.tests.DatabaseTestsBase$withDb$2.invoke(DatabaseTestsBase.kt:98) 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.tests.DatabaseTestsBase.withDb(DatabaseTestsBase.kt:120) at org.jetbrains.exposed.sql.tests.DatabaseTestsBase.withTables(DatabaseTestsBase.kt:138) at org.jetbrains.exposed.sql.tests.shared.ddl.CreateMissingTablesAndColumnsTests.testBug(CreateMissingTablesAndColumnsTests.kt:158) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecutor.runTestClass(JUnitTestClassExecutor.java:110) at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecutor.execute(JUnitTestClassExecutor.java:58) at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecutor.execute(JUnitTestClassExecutor.java:38) at org.gradle.api.internal.tasks.testing.junit.AbstractJUnitTestClassProcessor.processTestClass(AbstractJUnitTestClassProcessor.java:62) at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:51) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24) at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32) at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93) at com.sun.proxy.$Proxy2.processTestClass(Unknown Source) at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:118) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35) at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24) at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:175) at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:157) at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:404) at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:63) at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:46) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:55) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: org.postgresql.util.PSQLException: ОШИБКА: ограничение "fk_spaces_userid_id" для отношения "spaces" уже существует 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 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) ... 70 more

[freedom4live]

JdbcDatabaseMetadataImpl line 174. fromColumnName.
The column name from JDBC metadata is quoted, but nameInDatabaseCase() is fully in lower case and fails in IdentifierManagerApi line 47 shouldQuoteIdentifier.
It leads to case when column from metadata is quoted but nameInDatabaseCase is not. Iteration returns null.

The existed constraints are empty, the helper tries to alter table for adding a constraint.

[Auronake]

@Auronake , do you create tables on an empty database or tables already exists?

I've tried the two cases : empty or not database

@freedom4live, @Tapac, if the problem is on Master, I hope a quick fix about that :D. I believe in you :D

[Auronake]

Hi !

Have you an update for this merge request ? #936

@freedom4live , @Tapac

Thank you !

[Tapac]

Should be fixed in 0.26.1

[Tapac]

Should be fixed in 0.26.1