Postgresql 15 Array Type Support

[nichts12]

error:

2023-08-28 18:51:12,984 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (vert.x-eventloop-thread-0) HTTP Request to /request failed, error id: 1dea0f53-6add-4099-b03c-df768780ea1d-1: org.hibernate.HibernateException: Unable to extract JDBC value for position `4`
	at org.hibernate.reactive.sql.exec.spi.ReactiveValuesResultSet.lambda$readCurrentRowValues$2(ReactiveValuesResultSet.java:148)
	at java.base/java.util.concurrent.CompletableFuture.uniApplyNow(CompletableFuture.java:684)
	at java.base/java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:662)
	at java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:2168)
	at java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:144)
	at org.hibernate.reactive.sql.exec.spi.ReactiveValuesResultSet.readCurrentRowValues(ReactiveValuesResultSet.java:139)
	at java.base/java.util.concurrent.CompletableFuture$UniCompose.tryFire(CompletableFuture.java:1150)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510)
	at java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2147)
	at io.vertx.core.Future.lambda$toCompletionStage$3(Future.java:536)
	at io.vertx.core.impl.future.FutureImpl$3.onSuccess(FutureImpl.java:141)
	at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)
	at io.vertx.core.impl.future.FutureImpl.tryComplete(FutureImpl.java:211)
	at io.vertx.core.impl.future.PromiseImpl.tryComplete(PromiseImpl.java:23)
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryComplete(QueryResultBuilder.java:88)
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryComplete(QueryResultBuilder.java:32)
	at io.vertx.core.Promise.complete(Promise.java:66)
	at io.vertx.core.Promise.handle(Promise.java:51)
	at io.vertx.core.Promise.handle(Promise.java:29)
	at io.vertx.core.impl.future.FutureImpl$3.onSuccess(FutureImpl.java:141)
	at io.vertx.core.impl.future.FutureBase.lambda$emitSuccess$0(FutureBase.java:54)
	at io.netty.util.concurrent.AbstractEventExecutor.runTask(AbstractEventExecutor.java:174)
	at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:167)
	at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:470)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:569)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.lang.UnsupportedOperationException
	at org.hibernate.reactive.adaptor.impl.ResultSetAdaptor.getArray(ResultSetAdaptor.java:732)
	at cn.holmescraft.wahr.jelly.sticker.config.PostgreSqlStringArrayType.nullSafeGet(PostgreSqlStringArrayType.java:40)
	at cn.holmescraft.wahr.jelly.sticker.config.PostgreSqlStringArrayType.nullSafeGet(PostgreSqlStringArrayType.java:11)
	at org.hibernate.type.internal.UserTypeSqlTypeAdapter$ValueExtractorImpl.extract(UserTypeSqlTypeAdapter.java:110)
	at org.hibernate.reactive.sql.exec.spi.ReactiveValuesResultSet.lambda$readCurrentRowValues$2(ReactiveValuesResultSet.java:145)
	... 28 more

sql:

create table tb_note
(
    id           bigserial
        primary key,
    user_id      bigint                  not null,
    title        varchar(20)             not null,
    content      text,
    tag        varchar(255)[],
);

user type impl:

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.*;
import java.util.Arrays;

public class PostgreSqlStringArrayType implements UserType {

    @Override
    public int getSqlType() {
        return Types.ARRAY;
    }

    @Override
    public Class returnedClass() {
        return String[].class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x instanceof String[] && y instanceof String[]) {
            return Arrays.deepEquals((String[]) x, (String[]) y);
        } else {
            return false;
        }
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return Arrays.hashCode((String[]) x);
    }

    @Override
    public String[] nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session,
                                Object owner) throws SQLException {
        Array array = rs.getArray(position);
        return array != null ? (String[]) array.getArray() : null;
    }


    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
            throws HibernateException, SQLException {
        if (value != null && st != null) {
            Array array = session.getJdbcConnectionAccess().obtainConnection()
                    .createArrayOf("varchar", (String[]) value);
            st.setArray(index, array);
        } else {
            st.setNull(index, getSqlType());
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        String[] a = (String[]) value;
        return Arrays.copyOf(a, a.length);
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return original;
    }

}

entity:

...
@Type(PostgreSqlStringArrayType::class)
//    @JdbcTypeCode(SqlTypes.ARRAY)
    @Column(columnDefinition = "varchar(255)[]")
    private String[] tag;
...

I implemented UserType and hoped to get Array type data through ResultSet, but this method directly returned an exception.

This is ResultSetAdaptor class source:

...


	@Override
	public Array getArray(int columnIndex) {
		throw new UnsupportedOperationException();
	}


...

[DavideD]

Thanks.
I haven't look into this, but it might be related to this: #1641

[gavinking]

Well, it's not really #1641 because @nichts12 is using a UserType. But actually the UserType should be completely unnecessary, and just:

private String[] tag;

should work here, no annotations needed.

So I would just close this issue.

[DavideD]

Ah, I see that @Array is just to set the length of the array.

I'm not sure we have a test for mapping arrays, maybe we should add one before closing the issue.

[gavinking]

I'm not sure we have a test for mapping arrays

We don't, I don't think, and we do need to add it, because it looks like it's not working. But it's probably better to wait for HR to upgrade to ORM 6.3, where this stuff changed quite a lot (for the better).

[DavideD]

I already have a branch with ORM 6.3 but it requires the latest snapshot: #1726

That said, it can be used as base branch for issues requiring ORM 6.3

[nichts12]

2023-08-29 00:02:58,780 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (vert.x-eventloop-thread-1) HTTP Request to /main/recommend/note/0/10 failed, error id: 342eab39-d449-4a2f-a39e-134bcaddb8a0-1: org.hibernate.HibernateException: Unable to extract JDBC value for position `4`
	at org.hibernate.reactive.sql.exec.spi.ReactiveValuesResultSet.lambda$readCurrentRowValues$2(ReactiveValuesResultSet.java:148)
	at java.base/java.util.concurrent.CompletableFuture.uniApplyNow(CompletableFuture.java:684)
	at java.base/java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:662)
	at java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:2168)
	at java.base/java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:144)
	at org.hibernate.reactive.sql.exec.spi.ReactiveValuesResultSet.readCurrentRowValues(ReactiveValuesResultSet.java:139)
	at java.base/java.util.concurrent.CompletableFuture$UniCompose.tryFire(CompletableFuture.java:1150)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510)
	at java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2147)
	at io.vertx.core.Future.lambda$toCompletionStage$3(Future.java:536)
	at io.vertx.core.impl.future.FutureImpl$3.onSuccess(FutureImpl.java:141)
	at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)
	at io.vertx.core.impl.future.FutureImpl.tryComplete(FutureImpl.java:211)
	at io.vertx.core.impl.future.PromiseImpl.tryComplete(PromiseImpl.java:23)
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryComplete(QueryResultBuilder.java:88)
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryComplete(QueryResultBuilder.java:32)
	at io.vertx.core.Promise.complete(Promise.java:66)
	at io.vertx.core.Promise.handle(Promise.java:51)
	at io.vertx.core.Promise.handle(Promise.java:29)
	at io.vertx.core.impl.future.FutureImpl$3.onSuccess(FutureImpl.java:141)
	at io.vertx.core.impl.future.FutureBase.lambda$emitSuccess$0(FutureBase.java:54)
	at io.netty.util.concurrent.AbstractEventExecutor.runTask(AbstractEventExecutor.java:174)
	at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:167)
	at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:470)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:569)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.lang.UnsupportedOperationException
	at org.hibernate.reactive.adaptor.impl.ResultSetAdaptor.getArray(ResultSetAdaptor.java:732)
	at org.hibernate.reactive.type.descriptor.jdbc.ReactiveArrayJdbcType$2.doExtract(ReactiveArrayJdbcType.java:114)
	at org.hibernate.type.descriptor.jdbc.BasicExtractor.extract(BasicExtractor.java:44)
	at org.hibernate.reactive.sql.exec.spi.ReactiveValuesResultSet.lambda$readCurrentRowValues$2(ReactiveValuesResultSet.java:145)
	... 28 more

I have tried without adding any annotation, but the have same error.

[adampoplawski]

@DavideD @nichts12 @gavinking
Not sure if this is any benefit for you guys, but config that worked for us (Quarkus 3.4.3)
`
class PostgreSqlBrandListType : UserType<List> {

override fun equals(x: List<Brand>?, y: List<Brand>?): Boolean {
    return if (x is List<Brand> && y is List<Brand>) {
        x == y
    } else {
        false
    }
}

override fun hashCode(x: List<Brand>?): Int {
    if (x == null) return 0

    var hashCode = 1
    for (element in x) {
        hashCode = 31 * hashCode + (element.hashCode())
    }

    return hashCode
}

override fun getSqlType(): Int {
    return Types.ARRAY
}

override fun returnedClass(): Class<List<Brand>> {
    return List::class.java as Class<List<Brand>>
}

override fun nullSafeGet(
    rs: ResultSet,
    position: Int,
    session: SharedSessionContractImplementor?,
    owner: Any?
): List<Brand>? {
    val array = rs.getObject(position) as? Array<String> ?: return null
    val listOfBrands = array.toList()
    return listOfBrands.map { Brand.valueOf(it) }
}

override fun isMutable(): Boolean {
    return false
}

override fun assemble(cached: Serializable?, owner: Any?): List<Brand> {
    throw UnsupportedOperationException("Unable to assemble cached data")
}

override fun disassemble(value: List<Brand>?): Serializable {
    if (value != null) {
        return ArrayList(value)
    } else {
        throw UnsupportedOperationException("Unable to disassemble null value")
    }
}

override fun deepCopy(value: List<Brand>?): List<Brand> {
    if (value != null) {
        return ArrayList(value)
    } else {
        throw UnsupportedOperationException("Unable to deep copy null value")
    }
}

override fun nullSafeSet(
    st: PreparedStatement?,
    value: List<Brand>?,
    index: Int,
    session: SharedSessionContractImplementor?
) {
    if (value == null) {
        st!!.setNull(index, Types.ARRAY)
    } else {
        val array = value.toTypedArray()
        st!!.setObject(index, array)
    }
}

}

`

[blafond]

I've pushed a commit that fixes the UnsupportedOperationException, however the array support in Reactive still needs a lot of work.

I've added a test that checks some basic entity element java type arrays. As @gavinking mentioned there's been a lot of array work done in ORM's 6.2/6.3 and we'll have to take a deeper look and plan our approach to enable the array feature set in Reactive.

[DavideD]

@blafond where did you push the commit?

[blafond]

https://github.com/blafond/hibernate-reactive/commits/issue_1732_array_type_test