search

JetBrains / Exposed

Kotlin SQL Framework
http://jetbrains.github.io/Exposed/
Apache License 2.0
8.25k stars 689 forks source link

Redshift feature not supported #711

Open scorsi opened 4 years ago

scorsi commented 4 years ago

Hello guys,

I'm using Exposed with AWS Amazon Redshift and got exceptions when inserting data.

The code is as simple as that (I'm using a forEach on insert instead of batchInsert because I have another issue with batchInsert which will be issued in the github if this issue is fixed :) ) :

object FirstContacts : Table("some.table") {
    val id = varchar("contact_id", 36) references BaseContacts.id
    val appId = varchar("app_id", 128)
    val creationDate = datetime("creationdate")
    val firstname = varchar("firstname", 128).nullable()
    val lastname = varchar("lastname", 128).nullable()
    val phone = varchar("phone", 42).nullable()
    val gender = char("gender").nullable()
    val incomeRaw = integer("income_raw").nullable()
    val income = integer("income").nullable()
    val birthdateRaw = date("birthdate_raw").nullable()
    val birthdate = integer("birthdate").nullable()
}

notExistingFirstContacts.forEach { d ->
    @Suppress("RemoveRedundantQualifierName")
    FirstContacts.insert {
        it[FirstContacts.id] = d.first
        it[FirstContacts.appId] = appId
        it[FirstContacts.firstname] = d.second["firstname"]
        it[FirstContacts.lastname] = d.second["lastname"]
        it[FirstContacts.gender] = d.second["gender"]?.get(0)
    }
}

Here the exception thrown:

SQL: INSERT INTO some.table (app_id, birthdate, birthdate_raw, contact_id, firstname, gender, income, income_raw, lastname, phone) VALUES ('sensitivedata', NULL, NULL, 'sensitivedata', NULL, NULL, NULL, NULL, NULL, NULL)
[main] WARN Exposed - Transaction attempt #0 failed: java.sql.SQLFeatureNotSupportedException: [Amazon][JDBC](10220) Cette fonctionnalité optionnelle n’est pas prise en charge par le pilote.. Statement(s): INSERT INTO some.table (app_id, birthdate, birthdate_raw, contact_id, firstname, gender, income, income_raw, lastname, phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLFeatureNotSupportedException: [Amazon][JDBC](10220) Cette fonctionnalité optionnelle n’est pas prise en charge par le pilote.
SQL: [INSERT INTO some.table (app_id, birthdate, birthdate_raw, contact_id, firstname, gender, income, income_raw, lastname, phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:50)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:122)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:108)
    at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:29)
    at org.jetbrains.exposed.sql.QueriesKt.insert(Queries.kt:45)
    at io.adfinitas.prometer.importProcess.Import$insertFirstContacts$1.invoke(Import.kt:127)
    at io.adfinitas.prometer.importProcess.Import$insertFirstContacts$1.invoke(Import.kt:27)
    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 io.adfinitas.prometer.importProcess.Import.insertFirstContacts(Import.kt:114)
    at io.adfinitas.prometer.importProcess.Import.access$insertFirstContacts(Import.kt:27)
    at io.adfinitas.prometer.importProcess.Import$doImport$1.invokeSuspend(Import.kt:78)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
    at arrow.typeclasses.MonadContinuation$bind$$inlined$suspendCoroutineUninterceptedOrReturn$lambda$1.invoke(MonadContinuations.kt:36)
    at arrow.typeclasses.MonadContinuation$bind$$inlined$suspendCoroutineUninterceptedOrReturn$lambda$1.invoke(MonadContinuations.kt:15)
    at arrow.fx.IO$flatMap$1.invoke(IO.kt:620)
    at arrow.fx.IO$flatMap$1.invoke(IO.kt:50)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:295)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.signal(IORunLoop.kt:414)
    at arrow.fx.IORunLoop$RestartCallback.resumeWith(IORunLoop.kt:445)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
    at kotlin.coroutines.ContinuationKt.startCoroutine(Continuation.kt:114)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:402)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:227)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.signal(IORunLoop.kt:414)
    at arrow.fx.IORunLoop$RestartCallback.resumeWith(IORunLoop.kt:445)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
    at kotlin.coroutines.ContinuationKt.startCoroutine(Continuation.kt:114)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:402)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:227)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.signal(IORunLoop.kt:414)
    at arrow.fx.IORunLoop$RestartCallback.resumeWith(IORunLoop.kt:445)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
    at kotlin.coroutines.ContinuationKt.startCoroutine(Continuation.kt:114)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:402)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:227)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$suspendAsync$1.invoke(IORunLoop.kt:145)
    at arrow.fx.IORunLoop$suspendAsync$1.invoke(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:397)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:218)
    at arrow.fx.IORunLoop.start(IORunLoop.kt:24)
    at arrow.fx.IO.unsafeRunAsync(IO.kt:796)
    at arrow.fx.internal.Platform.unsafeResync(Utils.kt:156)
    at arrow.fx.IO$Async.unsafeRunTimedTotal$arrow_fx(IO.kt:1017)
    at arrow.fx.IO.unsafeRunTimed(IO.kt:862)
    at arrow.fx.IO.unsafeRunSync(IO.kt:851)
    at io.adfinitas.prometer.importProcess.MainKt.handleOrder(Main.kt:53)
    at io.adfinitas.prometer.importProcess.MainKt.main(Main.kt:62)
    at io.adfinitas.prometer.importProcess.MainKt.main(Main.kt)
Caused by: java.sql.SQLFeatureNotSupportedException: [Amazon][JDBC](10220) Cette fonctionnalité optionnelle n’est pas prise en charge par le pilote.
    at com.amazon.exceptions.ExceptionConverter.toSQLException(Unknown Source)
    at com.amazon.jdbc.common.SConnection.prepareStatement(Unknown Source)
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:323)
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
    at org.jetbrains.exposed.sql.statements.jdbc.JdbcConnectionImpl.prepareStatement(JdbcConnectionImpl.kt:54)
    at org.jetbrains.exposed.sql.statements.InsertStatement.prepared(InsertStatement.kt:137)
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:48)
    ... 59 more

My gradle config (I'm using HikariCP over Exposed) :

// HIKARICP
compile "com.zaxxer:HikariCP:3.4.1"

// EXPOSED
compile "org.jetbrains.exposed:exposed-core:0.18.1"
compile "org.jetbrains.exposed:exposed-java-time:0.18.1"
compile "org.jetbrains.exposed:exposed-jdbc:0.18.1"

Do you have some idea how I can bypass that feature or fix that exception ?

Thank you

Tapac commented 4 years ago

Redshift was never tested with Exposed and not listed as a supported dialect. Which connection url prefix do you use?

scorsi commented 4 years ago

Hello, to be able to use Exposed with Redshift I used the postgresql prefix since Redshift is based on Postgres 8 or 9

Tapac commented 4 years ago

The problem is that I don't have a possibility to run tests in AWS with Redshift setup. Maybe it will be possible a bit later.

scorsi commented 4 years ago

What I don't understand is that it appear that ? is the "thing not supported" in INSERT INTO some.table (app_id, birthdate, birthdate_raw, contact_id, firstname, gender, income, income_raw, lastname, phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) request ? But the following code is working great with the ? thing:

DriverManager.getConnection(
    "jdbc:redshift://some-redshift-cluster.eu-central-1.redshift.amazonaws.com:5439/somedb",
    "someuser",
    "somepassword"
).use { redshiftClient ->
    try {
        redshiftClient
            .prepareStatement("CREATE TABLE public.some_table (some_field VARCHAR(128), some_other_field INT);")
            .execute()
            .also { println("CREATE TABLE : $it") }
        redshiftClient
            .prepareStatement("INSERT INTO public.some_table VALUES (?, ?);")
            .apply {
                setString(1, "somevalue")
                setInt(2, 42)
            }
            .execute()
            .also { println("INSERT : $it") }
        redshiftClient
            .prepareStatement("SELECT * FROM public.some_table WHERE some_field = ?;")
            .apply {
                setString(1, "somevalue")
            }
            .executeQuery()
            .use { rs ->
                while (rs.next()) {
                    println("SELECT DATA: ${rs.getString("some_field")} ${rs.getString("some_other_field")}")
                }
            }
    } catch (e: Exception) {
        e.printStackTrace()
    } finally {
        redshiftClient
            .prepareStatement("DROP TABLE IF EXISTS public.some_table;")
            .execute()
            .also { println("DROP TABLE : $it") }
    }
}

May be the error isn't well explicit and the "not supported feature" is something else ? Does it come from the Redshift JDBC driver or from Exposed ? Don't know. I tried to figured it out by myself by looking directly at the Exposed source code but it's too heavy for a newbie in that source code. I don't well understanding how Exposed works and what it do with the JDBC driver and SQL queries. Do you have any idea of what is making problems here ?

scorsi commented 4 years ago

I switched the Redshift JDBC driver to the official PostgreSQL JDBC driver compatible with Pgsql 8.0.2 (see : https://docs.aws.amazon.com/fr_fr/redshift/latest/dg/c_redshift-and-postgres-sql.html). And I got better stack trace and errors !

[main] WARN Exposed - Transaction attempt #0 failed: org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.. Statement(s): INSERT INTO reveal.contacts_first (app_id, birthdate, birthdate_raw, contact_id, firstname, gender, income, income_raw, lastname, phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.
SQL: [INSERT INTO reveal.contacts_first (app_id, birthdate, birthdate_raw, contact_id, firstname, gender, income, income_raw, lastname, phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:50)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:122)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:108)
    at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:29)
    at org.jetbrains.exposed.sql.QueriesKt.insert(Queries.kt:45)
    at io.adfinitas.prometer.importProcess.Import$insertFirstContacts$1.invoke(Import.kt:127)
    at io.adfinitas.prometer.importProcess.Import$insertFirstContacts$1.invoke(Import.kt:27)
    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 io.adfinitas.prometer.importProcess.Import.insertFirstContacts(Import.kt:114)
    at io.adfinitas.prometer.importProcess.Import.access$insertFirstContacts(Import.kt:27)
    at io.adfinitas.prometer.importProcess.Import$doImport$1.invokeSuspend(Import.kt:78)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
    at arrow.typeclasses.MonadContinuation$bind$$inlined$suspendCoroutineUninterceptedOrReturn$lambda$1.invoke(MonadContinuations.kt:36)
    at arrow.typeclasses.MonadContinuation$bind$$inlined$suspendCoroutineUninterceptedOrReturn$lambda$1.invoke(MonadContinuations.kt:15)
    at arrow.fx.IO$flatMap$1.invoke(IO.kt:620)
    at arrow.fx.IO$flatMap$1.invoke(IO.kt:50)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:295)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.signal(IORunLoop.kt:414)
    at arrow.fx.IORunLoop$RestartCallback.resumeWith(IORunLoop.kt:445)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
    at kotlin.coroutines.ContinuationKt.startCoroutine(Continuation.kt:114)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:402)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:227)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.signal(IORunLoop.kt:414)
    at arrow.fx.IORunLoop$RestartCallback.resumeWith(IORunLoop.kt:445)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
    at kotlin.coroutines.ContinuationKt.startCoroutine(Continuation.kt:114)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:402)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:227)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.signal(IORunLoop.kt:414)
    at arrow.fx.IORunLoop$RestartCallback.resumeWith(IORunLoop.kt:445)
    at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
    at kotlin.coroutines.ContinuationKt.startCoroutine(Continuation.kt:114)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:402)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:227)
    at arrow.fx.IORunLoop.access$loop(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$suspendAsync$1.invoke(IORunLoop.kt:145)
    at arrow.fx.IORunLoop$suspendAsync$1.invoke(IORunLoop.kt:21)
    at arrow.fx.IORunLoop$RestartCallback.start(IORunLoop.kt:397)
    at arrow.fx.IORunLoop.loop(IORunLoop.kt:218)
    at arrow.fx.IORunLoop.start(IORunLoop.kt:24)
    at arrow.fx.IO.unsafeRunAsync(IO.kt:796)
    at arrow.fx.internal.Platform.unsafeResync(Utils.kt:156)
    at arrow.fx.IO$Async.unsafeRunTimedTotal$arrow_fx(IO.kt:1017)
    at arrow.fx.IO.unsafeRunTimed(IO.kt:862)
    at arrow.fx.IO.unsafeRunSync(IO.kt:851)
    at io.adfinitas.prometer.importProcess.MainKt.handleOrder(Main.kt:59)
    at io.adfinitas.prometer.importProcess.MainKt.main(Main.kt:68)
    at io.adfinitas.prometer.importProcess.MainKt.main(Main.kt)
Caused by: org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.
    at org.postgresql.jdbc3.AbstractJdbc3Statement.addReturning(AbstractJdbc3Statement.java:151)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:362)
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:323)
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
    at org.jetbrains.exposed.sql.statements.jdbc.JdbcConnectionImpl.prepareStatement(JdbcConnectionImpl.kt:54)
    at org.jetbrains.exposed.sql.statements.InsertStatement.prepared(InsertStatement.kt:137)
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:48)
    ... 59 more

Ad we can see, in Exposed you use the RETURNING keyword/feature which is not supported in Pgsql before 8.2 (as Redshift is based on 8.0.2, it doesn't support it too). Do you know if it's possible to disable that feature to make Redshift compatible with Exposed ? :)

scorsi commented 4 years ago

I think that I found where the INSERT query are built with the RETURNING keyword/feature. In org.jetbrains.exposed.sql.statements.InsertStatement:137, we have:

override fun prepared(transaction: Transaction, sql: String): PreparedStatementApi = when {
    // https://github.com/pgjdbc/pgjdbc/issues/1168
    // Column names always escaped/quoted in RETURNING clause
    autoIncColumns.isNotEmpty() && currentDialect is PostgreSQLDialect ->
        transaction.connection.prepareStatement(sql, true)

    autoIncColumns.isNotEmpty() ->
        // http://viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/
        transaction.connection.prepareStatement(sql, autoIncColumns.map { it.name.inProperCase() }.toTypedArray())

    else ->
        transaction.connection.prepareStatement(sql, true)
}

The else branch of the when is sending true as parameter to returnKeys (which is the feature missing in Redshift). We have the following possibilities :

I'm proposing so:

override fun prepared(transaction: Transaction, sql: String): PreparedStatementApi = when {
    // https://github.com/JetBrains/Exposed/issues/711
    // Redshift is not supporting RETURNING keyword/feature
    currentDialect is RedshiftDialect ->
        transaction.connection.prepareStatement(sql, false) /// MODIFICATION HERE

    // https://github.com/pgjdbc/pgjdbc/issues/1168
    // Column names always escaped/quoted in RETURNING clause
    autoIncColumns.isNotEmpty() && currentDialect is PostgreSQLDialect ->
        transaction.connection.prepareStatement(sql, true)

    autoIncColumns.isNotEmpty() ->
        // http://viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/
        transaction.connection.prepareStatement(sql, autoIncColumns.map { it.name.inProperCase() }.toTypedArray())

    else ->
        transaction.connection.prepareStatement(sql, false) /// MODIFICATION HERE
}

Does it may fix the problem ? Did I see it right ?

Tapac commented 4 years ago

I'm not sure what it's good as you can't use DAO and insertAndGetId-like functions.

Could you please test your PR locally by running SamplesDao.kt with redshift connector?

scorsi commented 4 years ago

We can see that Redshift is too far from standard SQL and from PostgreSQL and may be complicated to be compatible with Exposed by looking at this :

I just tried all the tests in SamplesSQL.kt or SamplesDao.kt, but the more issues I fixed, the more other it comes... I made too much changements in the test files and other part of Exposed without being able to make the tests work successfully.

Redshift is definitely too hard to make it compatible with Exposed without a lot of changements and missing features inside the core of Exposed itself :

Btw, I think you're agree with me, supporting Redshift may be too complicated for Exposed and my PR will not resolve all issues with Redshift... It's a long hard work which implies a lot of changements... It requires a big workload to support Redshift.

We may close that issue and the PR #714 except if someone has the workload to handle that issue... I will stop using Exposed with Redshift and turn back to standalone/vanilla JDBC.

Thank you

Tapac commented 4 years ago

@scorsi thank you for spending time on a deep investigation and attempt to workaround Redshift issues. I will leave that issue as is with a hope what Amazon will improve Redshift and jdbc-driver as well.