bug: use java JDBC insert array type error

[ediconss]

Describe the bug

my table:

CREATE TABLE ods.ods_order_detail (
	id int8,
	order_id int8, 
	abnormal_label int8[]
);

use java jdbc insert

  Connection connection = dataSource.getConnection();
  PreparedStatement preparedStatement = connection.prepareStatement(
          "insert into ods.ods_order_detail " +
          "(id, order_id,abnormal_label) values" +
          " (?, ?, ?)");
  preparedStatement.setInt(1 ,1233345);
  preparedStatement.setInt(2 ,1233);
  final Array arrayValue = connection.createArrayOf("bigint", new Long[]{1L,2L,3L});
  preparedStatement.setArray(3, arrayValue);
  preparedStatement.execute();
  connection.commit();
  connection.close();

Error message/log

org.postgresql.util.PSQLException: ERROR: Unsupported data type: bigint[]

	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

docker
PostgreSQL 9.5-RisingWave-1.6.0 (71898b4)
postgresql-42.3.3.jar

Additional context

No response

[xiangjinwu]

Likely due to lack of binary format support: #7949

Try switching to text mode with binaryTransfer=false from the driver.
https://jdbc.postgresql.org/documentation/use/#:~:text=queries%20are%20performed.-,binaryTransfer,-(boolean)

[ediconss]

Likely due to lack of binary format support: #7949

Try switching to text mode with binaryTransfer=false from the driver. https://jdbc.postgresql.org/documentation/use/#:~:text=queries%20are%20performed.-,binaryTransfer,-(boolean)

still can't
jdbc:postgresql://127.0.0.1:4566/dev?binaryTransfer=false

[xiangjinwu]

https://github.com/pgjdbc/pgjdbc/blob/REL42.7.1/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L1184-L1193

You are right. pgJDBC setArray would always use toBytes as long as PgArray is passed, ignoring the binaryTransfer flag in this case.

We are going to support binary encoding for array and struct anyways, but seems there is no simple workaround (as in go or python) before that is implemented - unless you are in a hurry and do not mind defining your own wrapper class that delegates to an inner PgArray but not identified by setArray as PgArray.

[ediconss]

okey，i wrapped, it's ok

[xiangjinwu]

See above for workaround. Will be resolved completely by #7949