bindArray doesn't work for bytea[] in Postgres
#2109
Comments
|
This actually seems to be a postgres issue. The driver code reads (In ArrayEncoding.java) } else if (array[i].getClass().isArray()) {
if (array[i] instanceof byte[]) {
throw new UnsupportedOperationException("byte[] nested inside Object[]");
}
try {
getArrayEncoder(array[i]).appendArray(sb, delim, array[i]);so they explicitly forbid an array of ARRAY_CLASS_TO_ENCODER.put(byte[].class, BYTEA_ARRAY);so I am literally clueless why the driver does not support it. |
Add an explicit array type that creates the postgres internal BYTEA format so that the driver is willing to write arrays. Fixes jdbi#2109, workaround for the problem in pgjdbc/pgjdbc#2630
|
can you try Jdbi.create(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
.withHandle(h -> {
h.registerArrayType(new ByteaArrayType());
h.execute("CREATE TABLE tbl (arr bytea[])");
h.createUpdate("INSERT INTO tbl (arr) VALUES (:arr)")
.bindArray("arr", new byte[]{1, 2})
.execute();
return null;
});
public final class ByteaArrayType implements SqlArrayType<byte[]> {
@Override
public String getTypeName() {
return "bytea";
}
@Override
public Object convertArrayElement(byte[] element) {
return PGbytea.toPGString(element);
}
}and tell me whether that changes / improves your situation? |
|
The code seems to work, thanks. Though it is sub-optimal because text encoding is used (this is crucial for me to have the most efficient format). For my task this workaround was good enough (Kotlin): registerArgument(object : ArgumentFactory.Preparable {
override fun prepare(type: Type, config: ConfigRegistry): Optional<Function<Any?, Argument>> {
val elementType = IterableLike.elementTypeOf(type)
.orElse(null) ?: return Optional.empty()
val sqlArrType = config.get(SqlArrayTypes::class.java).findFor(elementType)
.orElse(null) ?: return Optional.empty()
@Suppress("UNCHECKED_CAST")
sqlArrType as SqlArrayType<Any>
return Optional.of(
Function<Any?, Argument> { array ->
if (array == null) {
NullArgument(Types.ARRAY)
} else {
var homogeneous = true
var effectiveElementType: Class<*>? = null
val elements = IterableLike.stream(array)
.map {
sqlArrType.convertArrayElement(it).also { v ->
if (effectiveElementType == null) {
effectiveElementType = v.javaClass
} else if (homogeneous) {
homogeneous = v.javaClass == effectiveElementType
}
}
}
.collect(Collectors.toList())
if (!homogeneous) {
effectiveElementType = null
}
val arr = if (effectiveElementType == null) {
elements.stream().toArray()
} else {
elements.stream().toArray { len ->
@Suppress("UNCHECKED_CAST")
java.lang.reflect.Array.newInstance(effectiveElementType, len) as Array<Any>
}
}
Argument { position, statement, ctx ->
when (ctx.sqlArrayArgumentStrategy) {
SqlArrayArgumentStrategy.SQL_ARRAY -> {
val sqlArray = statement.connection.createArrayOf(sqlArrType.typeName, arr)
ctx.addCleanable { sqlArray.free() }
statement.setArray(position, sqlArray)
}
SqlArrayArgumentStrategy.OBJECT_ARRAY -> statement.setObject(position, array)
null -> throw NullPointerException()
}
}
}
}
)
}
})It works for all "special" array types and the driver happily uses binary encoding for non-empty arrays. I think this is what the driver should be doing. |
Add an explicit array type that creates the postgres internal BYTEA format so that the driver is willing to write arrays. Fixes jdbi#2109, workaround for the problem in pgjdbc/pgjdbc#2630
Add an explicit array type that creates the postgres internal BYTEA format so that the driver is willing to write arrays. Fixes jdbi#2109, workaround for the problem in pgjdbc/pgjdbc#2630
|
My understanding is that this is just the wire format between the driver and the database. The data itself ends up in the same shape on disk whether you send it in the "text" format or as binary (in that case, there is a some recoding but I did not really follow the driver code too closely). Does this cause a problem with the wire format or does the data end up differently in the database? In any case, if this fixes your problem, I will add this fix to the code base so starting with the next release, this should work out of the box. |
Add an explicit array type that creates the postgres internal BYTEA format so that the driver is willing to write arrays. Fixes jdbi#2109, workaround for the problem in pgjdbc/pgjdbc#2630
Yes, it's about the wire format. Text form is larger and harder to parser compared to binary form where it can just copy bytes directly. |
JDBI version: 3.32.0
Postgres driver:
org.postgresql:postgresql:42.5.0Reproducer:
Throws:
Not sure whether this is a Postgres driver issue, but it wants
bytea[]to be of Java typebyte[][]but JDBI erases it toObject[]. For other primitive arrays it appears to work fine when converted toObject[]but also precludes the use of more efficient binary encoding.The text was updated successfully, but these errors were encountered: