Apologize if this has been answered before, but I ...

# exposed

[Karl Azzam]

Apologize if this has been answered before, but I am trouble inserting into a table with a column named path of type ltree. I don't see ltree column support out of the box, and i've tried to tinker with a custom data type for an ltree column with no luck. Any insight here please on how to add support for an ltree column type?

[Karl Azzam]

This code doesnt work when trying to insert into the Foo table ,gives error: ERROR: column \"path\" is of type FooTable.ltree but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\

class LTreeType : ColumnType() {
    override fun sqlType(): String = "LTREE"
}

fun Table.ltree(name: String): Column<String> = registerColumn(name, LTreeType())

object FooTable : UUIDTable("foo_table") {
    val name = text("name")
    val path = ltree("path")
}

FooTable.insert {
    it[name] = "Bar"
    it[path] = "1"
}

[Chantal Loncle]

Hi @Karl Azzam Based on what I'm seeing about the ltree data type, extending any of the open string column types should be sufficient.

Then, to overcome the casting issue, a PGobject needs to be provided to properly describe the JDBC unknown type when setting the object in any prepared statement:

class LTreeType : StringColumnType() {
    override fun sqlType(): String = "LTREE"

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val parameterValue: PGobject? = value?.let {
            PGobject().apply {
                type = sqlType()
                this.value = value as? String
            }
        }
        super.setParameter(stmt, index, parameterValue)
    }
}

Tested this class with some custom operators and it produces expected results for the basic example in the above-linked reference docs.

[Karl Azzam]

@Chantal Loncle thanks! im able to insert into the table now but now getting an error that the result set didnt close (using exposed on top of agroal connection pool) after each invokation of inserting a record into this FooTable with the ltree column

WARN [io.agr.pool] (DefaultDispatcher-worker-1) Datasource '<default>': JDBC resources leaked: 1 ResultSet(s) and 0 Statement(s)

i am also doing the insert in a transaction block like always, here's my loggedTransaction (using suspend/coroutines). This should in theory close out all prepared statements and result sets under the hood.

suspend fun <T> loggedTransaction(statement: Transaction.() -> T): T {
    return suspendedTransactionAsync(Dispatchers.IO) {
        addLogger(StdOutSqlLogger)
        statement()
    }.await()
}

and here's an example write:

suspend fun exampleInsert(): Result<Unit> {
    return try {
        loggedTransaction {
            FooTable.insert {
                it[name] = "Bar"
                it[path] = "1"
            }
        }
        Result.success(Unit)
    } catch (ex: ExposedSQLException) {
        Log.error(ex)
        Result.failure(ex)
    }
}

[Chantal Loncle]

And you're not getting that error when you insert into any table that doesn't have the ltree column (or in the same table with that column removed)?

[Karl Azzam]

ah hah! actually i am getting the same memory leak with a normal table without the ltree column

[Karl Azzam]

im using quarkus as my framework, with agroal as the datasource. im going to downgrade quarkus to a previous version, looks like the latest "LTS" version has an issue with agroal

[Karl Azzam]

@Chantal Loncle i confirmed its an issue with quarkus (and agroal) in their LTS version 3.8.3, i did not get the same memory leak issue in quarkus 3.2.10.Final

[Karl Azzam]

thanks again for the help!

[Karl Azzam]

doing a select from FooTable does not work due to casting: (im guessing i have to override one of the other functions for a StringColumnType

class org.postgresql.util.PGobject cannot be cast to class java.lang.String (org.postgresql.util.PGobject is in unnamed module of loader 'app'; java.lang.String is in module java.base of loader 'bootstrap')"

[Karl Azzam]

I overrode the below fun valueFromDB and now it works for select/querying too

override fun valueFromDB(value: Any): Any {
        val parameterValue: PGobject =
            PGobject().apply {
                type = sqlType()
                this.value = value as? String
            }

        return when (value) {
            is String -> parameterValue
            else -> value.toString()
        }
    }