The Wayback Machine - https://web.archive.org/web/20201102125317/https://github.com/JetBrains/Exposed/issues/803
Skip to content

/ Exposed

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Postgresql uniqueIndex error with sheme. #803

Closed
DuchGhast opened this issue Feb 20, 2020 · 4 comments
Closed

Postgresql uniqueIndex error with sheme. #803

DuchGhast opened this issue Feb 20, 2020 · 4 comments
Labels
bug postgresql

Comments

@DuchGhast
Copy link

@DuchGhast DuchGhast commented Feb 20, 2020

This is happening only with scheme.

Code:

fun main()
{
	Database.connect(url = "jdbc:postgresql://localhost:5432/test_database",
					 driver = "org.postgresql.Driver",
					 user = "test_user",
					 password = "test_password")

	transaction {
		createSchema("user")
		SchemaUtils.createMissingTablesAndColumns(UsersTable)
	}
}

fun Transaction.createSchema(name: String) { exec("CREATE SCHEMA IF NOT EXISTS \"$name\"") }

object UsersTable : IntIdTable("user.users")
{
	val name = varchar("name", 200)
	val email = varchar("email", 100).uniqueIndex("users_email_unique")
	val password = varchar("password", 100)
}

Error:

15:53:49.891 [main] DEBUG Exposed - CREATE SCHEMA IF NOT EXISTS "users"
15:53:54.781 [main] INFO Exposed - Preparing create tables statements took 172ms
15:53:55.106 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS users.users (id SERIAL PRIMARY KEY, "name" VARCHAR(200) NOT NULL, email VARCHAR(100) NOT NULL, "password" VARCHAR(100) NOT NULL)
15:53:55.231 [main] DEBUG Exposed - ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
15:53:55.267 [main] INFO Exposed - Executing create tables statements took 486ms
15:53:55.674 [main] INFO Exposed - Extracting table columns took 407ms
15:53:55.722 [main] INFO Exposed - Extracting column constraints took 42ms
15:53:55.722 [main] INFO Exposed - Preparing alter table statements took 455ms
15:53:55.747 [main] DEBUG Exposed - ALTER TABLE users.users ADD id SERIAL PRIMARY KEY
15:53:55.761 [main] WARN Exposed - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERROR: column "id" of relation "users" already exists. Statement(s): ALTER TABLE users.users ADD id SERIAL PRIMARY KEY
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: column "id" of relation "users" 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:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:239)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: column "id" of relation "users" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	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)
	... 20 common frames omitted
15:53:56.051 [main] DEBUG Exposed - CREATE SCHEMA IF NOT EXISTS "users"
15:53:56.079 [main] INFO Exposed - Preparing create tables statements took 27ms
15:53:56.081 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS users.users (id SERIAL PRIMARY KEY, "name" VARCHAR(200) NOT NULL, email VARCHAR(100) NOT NULL, "password" VARCHAR(100) NOT NULL)
15:53:56.084 [main] DEBUG Exposed - ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
15:53:56.084 [main] WARN Exposed - Transaction attempt #1 failed: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists. Statement(s): ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" 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:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:231)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	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)
	... 20 common frames omitted
15:53:56.130 [main] DEBUG Exposed - CREATE SCHEMA IF NOT EXISTS "users"
15:53:56.183 [main] INFO Exposed - Preparing create tables statements took 25ms
15:53:56.187 [main] DEBUG Exposed - CREATE TABLE IF NOT EXISTS users.users (id SERIAL PRIMARY KEY, "name" VARCHAR(200) NOT NULL, email VARCHAR(100) NOT NULL, "password" VARCHAR(100) NOT NULL)
15:53:56.189 [main] DEBUG Exposed - ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
15:53:56.190 [main] WARN Exposed - Transaction attempt #2 failed: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists. Statement(s): ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" 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:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:231)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	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)
	... 20 common frames omitted
Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
SQL: [ALTER TABLE users.users ADD CONSTRAINT users_email_unique UNIQUE (email)]
	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:170)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:231)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:224)
	at MainKt$main$1.invoke(Main.kt:14)
	at MainKt$main$1.invoke(Main.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:103)
	at MainKt.main(Main.kt:12)
	at MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "users_email_unique" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
	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)
	... 20 more
@hfazai hfazai mentioned this issue Feb 22, 2020
Closed
@Suntrie Suntrie mentioned this issue Apr 11, 2020
Open
@DRSchlaubi
Copy link

@DRSchlaubi DRSchlaubi commented May 29, 2020

Any updates on this? also, why do we have to put the unique index there in the first place shouldn't exposed do it automatically when making a reference
@jnfeinstein
Copy link

@jnfeinstein jnfeinstein commented Jul 27, 2020 •
edited

I think I am receiving a related error when using SchemaUtils.createMissingTablesAndColumns.

SQL: [ALTER TABLE domainevententry ADD CONSTRAINT domainevententry_aggregateidentifier_sequencenumber_unique UNIQUE (aggregateidentifier, sequencenumber)]

results in:

org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: relation "domainevententry_aggregateidentifier_sequencenumber_unique" already exists

Kotlin: 1.3.71
Exposed: 0.26.1
PostgreSQL: 10.7 via AWS Aurora Serverless

/* schema.XXX returns string names for 3rd party table */

object AxonDomainEventEntryTable : LongIdTable(schema.domainEventTable(), schema.globalIndexColumn().toLowerCase()) {
    val aggregateIdentifier = varchar(schema.aggregateIdentifierColumn().toLowerCase(), 255)
    val sequenceNumber = long(schema.sequenceNumberColumn().toLowerCase())
    val type = varchar(schema.typeColumn().toLowerCase(), 255).nullable()
    val eventIdentifier = varchar(schema.eventIdentifierColumn().toLowerCase(), 255)
    val metadata = binary(schema.metaDataColumn().toLowerCase()).nullable()
    val payload = binary(schema.payloadColumn().toLowerCase())
    val payloadRevision = varchar(schema.payloadRevisionColumn().toLowerCase(), 255).nullable()
    val payloadType = varchar(schema.payloadTypeColumn().toLowerCase(), 255)
    val timestamp = varchar(schema.timestampColumn().toLowerCase(), 255)

    init {
        uniqueIndex(aggregateIdentifier, sequenceNumber)
        uniqueIndex(eventIdentifier)
    }
}
@jnfeinstein
Copy link

@jnfeinstein jnfeinstein commented Jul 27, 2020

Further data point: it appears to occur when there is another schema with the same collection of tables and indices.
Tapac added a commit that referenced this issue Jul 31, 2020
@Tapac
Postgresql uniqueIndex error with sheme. #803
Loading status checks…
9ecbcdf 
SchemeUtils.setScheme doesn't reset current scheme in ExposedDatabaseMetadata
@Tapac
Copy link
Contributor

@Tapac Tapac commented Aug 1, 2020

Should be fixed in the next release. Please report if issue will still exist.
Tapac added a commit that referenced this issue Aug 2, 2020
@Tapac
Postgresql uniqueIndex error with sheme. #803 / exclude SQL Server fr…
Loading status checks…
8b8f756 
…om test
@Tapac Tapac closed this Aug 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug postgresql
4 participants
@Tapac @jnfeinstein @DRSchlaubi @DuchGhast
You can’t perform that action at this time.