Cannot use ArcadeDB with Spring Data Repositories

[gfinger]

I cannot make ArcadeDB work with Spring Data Repositories. I have described the problem on StackOverflow.
As far as I can see the problem is connected with the transaction management of Spring Data. Is there any experience in the community about how to use ArcadeDB with Spring Data Repositories?

The error message on the Java client side is:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT COUNT(*) FROM Team]; nested exception is org.postgresql.util.PSQLException: ERROR: Syntax error on parsing query: Encountered " "READ "" at line 1, column 7.
Was expecting one of:

...
";" ...
...

on the server side the error message is:

Unexpected message type '' for message any
com.arcadedb.postgres.PostgresProtocolException: Unexpected message type '' for message any
at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:969)
at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:140)

2022-07-12 16:32:07.515 SEVER [PostgresNetworkExecutor] Closing connection with client

The server version I use is 22.6.2-SNAPSHOT

[lvca]

Unfortunately part of Postgres protocol are not well documented and our implementation is not 100% reliable with that protocol. Is there any way you can use the ArcadeDB Java API instead?

[lvca]

@gfinger If you can help on that... Man, it would be awesome! By the way, you can log the Postgres protocol by setting:

-Darcadedb.postgres.debug=true

At startup

[gfinger]

To me it looks like the postgresw plugin doesn't support read-only transactions and returns a syntax error. If read-only transactions are not supported the read-only flag should just be ignored.
This is what the log says:

2022-07-14 14:17:33.747 INFO [PostgresNetworkExecutor] PSQL: parse (portal=) -> BEGIN READ ONLY (params=0) (errorInTransaction=false thread=37)
2022-07-14 14:17:33.766 INFO [PostgresNetworkExecutor] PSQL:-> bind complete (2 - 4b) (thread=37)Unexpected message type '' for message any
com.arcadedb.postgres.PostgresProtocolException: Unexpected message type '' for message any
at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:969)
at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:140)

I think the empty message type is not the root cause but rather the result of erroneously handling the BEGIN READ ONLY. But I have to look deeper into it.

[lvca]

In ArcadeDB we don't have specific read only transactions, it would be a transaction that starts and rollback at the end. Did you find a read only transaction parameter in poatgres protocol?

[gfinger]

I have changed the PostgresNetworkExecutor to ignore the READ ONLY and just take it as a BEGIN with the result that there is no error on the server side any longer but the client is throwing an exception because something in the response is missing.
The Postgres message specification does not mention any transaction handling at all, at least not in this document here: https://www.postgresql.org/docs/current/protocol-message-formats.html. The expectation in Spring Data is that if a database doesn't support the READ ONLY mode it would just ignore it, but it seems that for the postgres driver just ignoring is not okay.
Thus it gets complicated. There are two possibilities: change the arcadedb-postgresw module to at least respond to the read only request in a way that satisfies the client, or find a way to configure spring data to not use the read only attribute for the transaction begin. Let's see.

[gfinger]

I configured the data source on the client side to not use read-only transactions, but now I get an issue with the detection of the default transaction isolation. The postgres driver sends a request "SHOW TRANSACTION ISOLATION", which is accepted on the server side, but no result is returned.
This is the log from the driver:

2022-07-15 11:47:13.464 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : FE=> Parse(stmt=null,query="SHOW TRANSACTION ISOLATION LEVEL",oids={})
2022-07-15 11:47:13.465 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : FE=> Bind(stmt=null,portal=null)
2022-07-15 11:47:13.465 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : FE=> Describe(portal=null)
2022-07-15 11:47:13.465 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : FE=> Execute(portal=null,limit=0)
2022-07-15 11:47:13.465 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : FE=> Sync
2022-07-15 11:47:13.467 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : <=BE ParseComplete [null]
2022-07-15 11:47:13.470 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : <=BE BindComplete [unnamed]
2022-07-15 11:47:13.473 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : <=BE NoData
2022-07-15 11:47:13.475 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : <=BE ReadyForQuery(T)
2022-07-15 11:47:13.475 TRACE 55226 --- [ main] o.postgresql.core.v3.QueryExecutorImpl : FE marking setPortalDescribed(false) for query SYNC
2022-07-15 11:47:13.493 WARN 55226 --- [ main] com.zaxxer.hikari.pool.PoolBase : HikariPool-1 - Default transaction isolation level detection failed (No results were returned by the query.).

And this is the log from the server:

2022-07-15 11:47:13.466 INFO [PostgresNetworkExecutor] PSQL: parse (portal=) -> SHOW TRANSACTION ISOLATION LEVEL (params=0) (errorInTransaction=false thread=55)
2022-07-15 11:47:13.467 INFO [PostgresNetworkExecutor] PSQL:-> parse complete (1 - 4b) (thread=55)
2022-07-15 11:47:13.468 INFO [PostgresNetworkExecutor] PSQL: bind (portal=) -> (thread=55)
2022-07-15 11:47:13.470 INFO [PostgresNetworkExecutor] PSQL:-> bind complete (2 - 4b) (thread=55)
2022-07-15 11:47:13.471 INFO [PostgresNetworkExecutor] PSQL: describe '' type=P (errorInTransaction=false thread=55)
2022-07-15 11:47:13.471 INFO [PostgresNetworkExecutor] PSQL: execute (portal=) (limit=0)-> SHOW TRANSACTION ISOLATION LEVEL (thread=55)
2022-07-15 11:47:13.473 INFO [PostgresNetworkExecutor] PSQL:-> no data (n - 4b) (thread=55)
2022-07-15 11:47:13.474 INFO [PostgresNetworkExecutor] PSQL: sync (thread=55)
2022-07-15 11:47:13.475 INFO [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=55)

It looks like there is quite a lot to do on the arcadedb-postgrew module to make it work with Spring, or actually there is not much Spring involved in this example, the error occurs in the Hikari connection pool when checking for the default transaction isolation.