Exposed

Exposed copied to clipboard

SQLDialect: PostgreSQLDialect

When using SchemaUtils.addMissingColumnsStatements() to verify that Tables written as Exposed objects are equal to tables in the existing Postgresql database, the function will return ALTER statements for columns with type timestamp if it has default value CURRENT_TIMESTAMP.

e.g: val createdAt = datetime("created_at").defaultExpression(CurrentDateTime())

Cause: There is no mapping for the PostgreSQLDialect in the org.jetbrains.exposed.sql.vendors.Default.processForDefaultValue function, so the expression will be wrapped with parentheses https://github.com/JetBrains/Exposed/blob/2092d3bcd2d5e5e7598b17d55c7a48826f889234/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/vendors/Default.kt#L104

This leads to a comparison between "CURRENT_TIMESTAMP" and "(CURRENT_TIMESTAMP)" here: https://github.com/JetBrains/Exposed/blob/2092d3bcd2d5e5e7598b17d55c7a48826f889234/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/SchemaUtils.kt#L210-L211 Which in turn returns false, and marks the column for ALTER, while in reality there is no difference.

Same issue on our side. Meantime, we will disable altering to be safe.

Same issue here. I came to the same conclusion as @MartinStigen before finding this issue. My suggestion would be adding another clause for functions to the when statement in Default.kt to remove the parentheses, i.e.

     open fun processForDefaultValue(e: Expression<*>): String = when {
         e is LiteralOp<*> -> "$e"
+        e is Function<*> -> "$e"
         currentDialect is MysqlDialect -> "$e"
         currentDialect is SQLServerDialect -> "$e"
         else -> "($e)"
     }

This should be safe since functions don't require wrapping, and operators (which are also functions) already include parentheses themselves:

val defaultValue = currentDialect.dataTypeProvider.processForDefaultValue(
    CustomOperator<Long>("+", LongColumnType(), longLiteral(1), longLiteral(1))
)
println(defaultValue)

((0 + 0))

Note the duplicate parentheses.

Hi, I have the same issue with supabase and postgress, the default function is set to: uuid_generate_v4()

I tried to use this default expression with Exposed defaultExpression(CustomFunction("uuid_generate_v4", UUIDColumnType()))

But the result have additional parentheses (uuid_generate_v4())

For anyone who is interested in a nasty workaround: I've wrapped the function statementsRequiredToActualizeScheme (which happens to suffer from the same problem) and filtered out unnecessary ALTER statements that only add the CURRENT_TIMESTAMP default value on columns which already have that default value.

fun statementsRequiredToActualizeScheme(vararg tables: Table, withLogs: Boolean = true): List<String> {
    val statements = SchemaUtils.statementsRequiredToActualizeScheme(*tables, withLogs = withLogs)
    val existingColumnsByTable = currentDialect.tableColumns(*tables)
    val allColumns = tables.map { table -> table.columns }.flatten()
    val problematicColumns = allColumns.filter { column ->
        val hasDefaultInCode = column.descriptionDdl().contains("DEFAULT (CURRENT_TIMESTAMP)")
        val existingColumn = existingColumnsByTable[column.table]?.singleOrNull {
            column.name.equals(it.name, true)
        }
        val hasDefaultInDb = existingColumn?.defaultDbValue == "CURRENT_TIMESTAMP"
        hasDefaultInCode && hasDefaultInDb
    }
    val problematicStatements = problematicColumns.map {
        currentDialect.modifyColumn(
            it,
            ColumnDiff(defaults = true, nullability = false, autoInc = false, caseSensitiveName = false),
        ).single()
    }
    return statements.filter { it !in problematicStatements }
}

+1

If you use CurrentTimestamp instead of CurrentDateTime, the error still occurs:

import org.jetbrains.exposed.sql.javatime.CurrentTimestamp

val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp())

This is due to CurrentTimestamp not inheriting from Function but only from Expression. This holds both for the Java and the Kotlin versions:

• Kotlin: https://github.com/JetBrains/Exposed/blob/be5b0485f4c622d4a8c32abda40f6ddc1a07ed26/exposed-kotlin-datetime/src/main/kotlin/org/jetbrains/exposed/sql/kotlin/datetime/KotlinDateFunctions.kt#L67
• Java: https://github.com/JetBrains/Exposed/blob/be5b0485f4c622d4a8c32abda40f6ddc1a07ed26/exposed-java-time/src/main/kotlin/org/jetbrains/exposed/sql/javatime/JavaDateFunctions.kt#L52

Please reopen the issue, (or should I open a new issue?)

@michael-markl Thanks for bringing this to our attention.

If you're willing, could you please open a new issue on YouTrack so we can look further into this?

@bog-walk I opened an issue: https://youtrack.jetbrains.com/issue/EXPOSED-112/SchemaUtils.addMissingColumnsStatements-fails-to-compare-timestamp-column-with-default-CURRENTTIMESTAMP-for-Postgresql