How can I create a custom GIN index in Postgres wi...

# exposed

[Thomas Ploch]

How can I create a custom GIN index in Postgres with native Exposed tooling? We use SchemaUtils to generate Flyway migrations, and custom SQL will always be reported as missing - even though they exist in the database.

[Chantal Loncle]

Hi @Thomas Ploch A GIN index can be created using Table.index() with the indexType parameter, which accepts any custom string value (in an init for example).

If you're using SchemaUtils to detect changes in this index, please note that index metadata comparison usually involves specifics like name, columns, and uniqueness. If you run into an issue because you need to trigger an alter/create for an index where only the type differs, please consider opening an issue on YouTrack with any relevant details.

[Thomas Ploch]

@Chantal Loncle this does not work, because GIN indexes in PostgreSQL require a so-called "operator class". This class cannot be set by the standard Exposed indexing mechanisms. The desired SQL looks like this:

CREATE INDEX IF NOT EXISTS $name ON $table USING $type ($columns $opClass) $filterCondition;

There are currently two ways to modify the existing index creation:

1. Using the Functions argument. This does not work since the functions' outputs are placed after a "," in the columns list: ... USING GIN (column, <function output>) - that's invalid due to the inserted ","
2. Using the filter conditions. This does not work as well, since we need to insert the operator class directly after the column name in the column list, and not behind it.
The only way was to hook into the custom PostgreSQL index generation code via extending the PostgreSQL dialect directly:

object ExtendedPostgreSQLDialect : PostgreSQLDialect() {
    override fun createIndexWithType(
        name: String,
        table: String,
        columns: String,
        type: String,
        filterCondition: String,
    ): String {
        if (type == "GIN") {
            val createExtension = "CREATE EXTENSION IF NOT EXISTS pg_trgm"
            val createIndex = "CREATE INDEX IF NOT EXISTS $name ON $table USING $type ($columns gin_trgm_ops) $filterCondition"

            return "$createExtension; $createIndex;"
        }

        return super.createIndexWithType(name, table, columns, type, filterCondition)
    }
}

Then, when connecting the Database, we need to set the new extended dialect explicitly:

object ExposedUtils {
    fun Database.Companion.connectWithDefaultConfig(dataSource: DataSource): Database =
        connect(
            dataSource,
            databaseConfig = DatabaseConfig {
                explicitDialect = ExtendedPostgreSQLDialect
                useNestedTransactions = true
            }
        )
}

Now we have the correct SQL generated, and all other Exposed features work as expected:

init {
    index(columns = arrayOf(column), customIndexName = "index_name_gin", indexType = "GIN")
}

I hope this helps other people faced with the same problem. But I am open to suggestions for a better solution.

[Chantal Loncle]

Apologies @Thomas Ploch I wasn't aware of the exact SQL that you needed and the reference docs that I used made no mention of an operator class being required.

The solution that you've detailed above, overriding the VendorDialect, is the recommended one for any database-specific index customizations until the Index class is made more open.