Disabling autoCommit breaks the driver

[awal11]

I am trying to use the driver together with PrestoDB. I encountered few issues, this is the last of them, with changes needed for it - the driver works!

The description of Transactions in Readme.md is misleading, it is actually the opposite, if you set conn.autoCommit(false) the queries will start failing - see details below.
If you use the driver as a part of bigger framework you might not be able to prevent the framework from switching the autoCommit to false.
Given that the whole driver is Read Only - the simplest option is to remove the ability to switch it in PgConnection. Then the the whole paragraph in Readme.md could be removed, will not make any sense.

Details

1. Executing conn.setAutocommit(false) switches the driver into transactional mode
2. the first query ("select col from table") will be automatically prefixed with BEGIN by QueryExecutor
3. and will fail with

org.postgresql.util.PSQLException: ERROR: XDMP-UNEXPECTED: (err:XPST0003) Unexpected token syntax error, unexpected "<id>", expecting "<end of file>"
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:301)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:287)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:264)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:231)

wich is difficult to trace as the message from ML is not very helpful

Without conn.setAutoCommit(false) it all works perfectly.

Let me know if I should create pull request that will remove the functionality to switch PgConnection.setAutoCommit()

[bobstarbird]

Possibly throw error
throw new PSQLException(GT.tr("AutoCommit false is not supported."),
PSQLState.NOT_IMPLEMENTED);

[awal11]

This might be ok, I have not encountered a framework that would set autocommit on its own. On the other hand, if the driver is read only all the time, no matter what - ignoring all the requests related to data modifications should be not harmful and a bit simpler. But you decide on the approach.

…

On Tue, 5 Feb 2019 at 17:06, bobstarbird ***@***.***> wrote: Possibly throw error throw new PSQLException(GT.tr("AutoCommit false is not supported."), PSQLState.NOT_IMPLEMENTED); — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#3 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AGQQxIJ_sAvTS52-tpmHelDPZZbIupuqks5vKavvgaJpZM4Z7tgb> .

[bobstarbird]

Proposed implementation:
public void setAutoCommit(boolean autoCommit) throws SQLException {
checkClosed();

if (this.autoCommit == autoCommit) {
  return;
}

if (!autoCommit) {
  throw new PSQLException(GT.tr("AutoCommit false setting is not supported."),
    PSQLState.NOT_IMPLEMENTED);
}
if (!this.autoCommit) {
  throw new PSQLException(GT.tr("AutoCommit false default is not supported."),
    PSQLState.NOT_IMPLEMENTED);
}

}

[bobstarbird]

QUERY_SUPPRESS_BEGIN controls emision of "BEGIN"
pgStatement.java:
if (connection.getAutoCommit()) { flags |= QueryExecutor.QUERY_SUPPRESS_BEGIN; }

[seyal84]

is this issue resolved? what's the solution

[bobstarbird]

Initially BEGIN was suppressed in the driver with code modifications.
Now MarkLogic Server 9.4+ silently handles BEGIN and changes were reverted.

[seyal84]

@bobstarbird Thanks for your inputs.

using mljdbc-42.1.4.jar (without suppress BEGIN - since the one uploaded in github is corrupted)

we are using 10.0-4.3. but still unable to run queries via JDBC/ODBC driver. We can establish the connectivity and see tables but getting the below error for some reason when we run a specific query to extract the data from ML :

TRACE LOGGING

Sep 10, 2020 10:35:41 AM org.postgresql.jdbc.PgConnection setAutoCommit
FINE: setAutoCommit = false

ERROR

Error: java.lang.RuntimeException: java.lang.RuntimeException: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:167)
at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:158)
at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:77)
at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:137)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:763)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1875)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168)
Caused by: java.lang.RuntimeException: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:220)
at org.apache.sqoop.mapreduce.db.DBInputFormat.setDbConf(DBInputFormat.java:165)
... 10 more
Caused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:327)
at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:53)
at org.postgresql.core.v3.ConnectionFactoryImpl.runInitialQueries(ConnectionFactoryImpl.java:651)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:247)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:194)
at com.marklogic.Driver.makeConnection(Driver.java:451)
at com.marklogic.Driver.connect(Driver.java:253)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:300)
at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:213)