Presto JDBC driver for watsonx iceberg fails with "Catalog iceberg_data only supports writes using autocommit" even though Presto allows to set databaseConnection.setAutcommit(false)

[RashmiBharadwaj]

Presto JDBC driver supports setting databaseConnection.setAutcommit(false), but then when I run a query like "insert into table values(...)", the query fails with error: Catalog iceberg_data only supports writes using autocommit.

I am using a presto jdbc driver presto-jdbc-0.292.jar, to connect to watsonx.data cpd version 2.1.3.
Iceberg connector is used here.

Sample java code to reproduce the issue:

String presto_url = "jdbc:presto://:8443/iceberg_data?SSL=true&SSLTrustStorePath=<cert_path>";
String driverClass = "com.facebook.presto.jdbc.PrestoDriver";
Class.forName(driverClass);
/** Set the connection properties**/
Properties properties = new Properties();
properties.setProperty("user", username);
properties.setProperty("password", password);

/** Connect **/
connection = DriverManager.getConnection(presto_url, properties);

DatabaseMetaData databaseMetaData = connection.getMetaData();
System.out.println("databaseMetaData.supportsTransactions(): " + databaseMetaData.supportsTransactions());
if(databaseMetaData.supportsTransactions())
{
connection.setAutoCommit(false);
}

System.out.println("connection.getAutoCommit(): " + connection.getAutoCommit());
Statement st = connection.createStatement();

String query = "insert into s33.t1 values(1,'a')";
System.out.println(query);
st.execute(query);

Error:

databaseMetaData.supportsTransactions(): true
connection.getAutoCommit(): false
insert into s33.t1 values(1,'a')
java.sql.SQLException: Query failed (#20250711_072159_00075_tn25v): Catalog iceberg_data only supports writes using autocommit
at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1841)
at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1751)
at com.facebook.presto.jdbc.PrestoResultSet.(PrestoResultSet.java:121)
at com.facebook.presto.jdbc.PrestoStatement.internalExecute(PrestoStatement.java:272)
at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:230)
at sampletest.samplewatsonx3.main(samplewatsonx3.java:50)
Caused by: com.facebook.presto.spi.PrestoException: Catalog iceberg_data only supports writes using autocommit
at com.facebook.presto.transaction.InMemoryTransactionManager$TransactionMetadata.checkConnectorWrite(InMemoryTransactionManager.java:561)
at com.facebook.presto.transaction.InMemoryTransactionManager.checkConnectorWrite(InMemoryTransactionManager.java:273)
at com.facebook.presto.transaction.InMemoryTransactionManager.getCatalogMetadataForWrite(InMemoryTransactionManager.java:236)
at com.facebook.presto.metadata.MetadataManager.getCatalogMetadataForWrite(MetadataManager.java:1767)
at com.facebook.presto.metadata.MetadataManager.getInsertLayout(MetadataManager.java:828)
at com.facebook.presto.sql.planner.LogicalPlanner.buildInternalInsertPlan(LogicalPlanner.java:364)
at com.facebook.presto.sql.planner.LogicalPlanner.createInsertPlan(LogicalPlanner.java:307)
at com.facebook.presto.sql.planner.LogicalPlanner.planStatementWithoutOutput(LogicalPlanner.java:175)
at com.facebook.presto.sql.planner.LogicalPlanner.planStatement(LogicalPlanner.java:157)
at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:140)
at com.facebook.presto.sql.analyzer.BuiltInQueryAnalyzer.plan(BuiltInQueryAnalyzer.java:127)
at com.facebook.presto.execution.SqlQueryExecution.lambda$createLogicalPlanAndOptimize$2(SqlQueryExecution.java:554)
at com.facebook.presto.common.RuntimeStats.profileNanos(RuntimeStats.java:136)
at com.facebook.presto.execution.SqlQueryExecution.createLogicalPlanAndOptimize(SqlQueryExecution.java:552)
at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:474)
at com.facebook.presto.$gen.Presto_0_286____20250707_040312_1.run(Unknown Source)
at com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:320)
at com.facebook.presto.dispatcher.LocalDispatchQuery.lambda$startExecution$8(LocalDispatchQuery.java:214)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:839)

Expected Behavior

Since the driver returns true for supportsTransactions(), query should work when autocommit is disabled, or presto has to return false when supportsTransactions() is called. Or presto should not allow users to change autocommit for a connection.

Context

In our application, we are using JDBC's DatabaseMetaData.supportsTransactions() to check if setting autocommit to false is allowed. There is no specific check based on driver type. Is there a way users can detect if setting autocommit is allowed or not for presto when connecting to watsonx.data/iceberg connector?

[imjalpreet]

I haven’t tested all connectors in Presto, but I think supportsTransactions() was set to return true because users can query across catalogs using the same JDBC connection, and a few connectors may support transactions. However, as far as I know, most connectors do not support transactions.

If autocommit is disabled and the connector doesn’t support transactions, Presto throws the following error:
Catalog <catalog_name> only supports writes using autocommit.

Is there a way users can detect if setting autocommit is allowed or not for presto when connecting to watsonx.data/iceberg connector?

Unfortunately, as far as I know, there’s no way to determine whether a connector supports transactions through the JDBC driver. If a connector doesn’t support transactions, Presto will throw the error mentioned above.

[pratyakshsharma]

#25003 this looks relevant to the ask here.

[RashmiBharadwaj]

@imjalpreet @pratyakshsharma can this can be fixed in presto driver? Perhaps we can escalate to add this to your roadmap as priority and get it fixed soon?

[hantangwangd]

As a federated query engine, Presto can utilize many connectors as data sources, and itself supports multi-statement writes transaction for a single target connector, that is, multiple write statements must target tables within the same connector. This is why supportsTransactions() returns true. But this transaction mechanism implicitly relies on the target connector to support multi-statement writes transaction. Meanwhile, some connectors (e.g. CassandraConnector, ClickHouseConnector, HudiConnector, IcebergConnector, etc.) themselves lack support for multi-statement writes transaction. Therefore, when actually executing writes to these connectors within an autocommit=false transaction, Presto will throw the aforementioned error. IMO, this shouldn't be seen as a bug, but rather an expected behavior. What do you think? @pratyakshsharma @imjalpreet @RashmiBharadwaj

As for Iceberg, thanks @pratyakshsharma for mentioning PR #25003, I'm currently exploring Iceberg community's catalog based multi-table transaction implementation details, and trying to support single-table multi-statement writes transaction in Presto Iceberg connector. For reference, check the following document and PR from Iceberg community:

https://docs.google.com/document/d/1UxXifU8iqP_byaW4E2RuKZx1nobxmAvc5urVcWas1B8/edit?tab=t.0#heading=h.qgby9losbola

apache/iceberg#6948

[phillipkoza]

supportTransactions() should only return true if the database ALWAYS supports transactions. If you only support transactions sometimes when certain connectors are used, then you need to return false, or you need to look at the underlying connectors to determine if you should return true or false. You should never claim more support than you actually have. It could be considered false advertising to claim you support something if you in fact only support it X% of the time. You could be held liable. Clients will be unpleasantly surprised to find out something you said would works does not. Whereas if you say you don't support something and then it does work, clients will be pleasantly surprised to find it does work.

[hantangwangd]

From what I understand, ALWAYS supports transactions means I can always use start transaction, commit or rollback to execute a transaction that may contain multiple statements, which Presto fully supports. We can refer to the comment for supportsTransactions in DatabaseMetadata here:

Retrieves whether this database supports transactions. If not, invoking the method commit is a noop, and the isolation level is TRANSACTION_NONE.

Presto can always start a transaction, and its commit/rollback is not a noop. However, this doesn't mean every statement within the transaction must execute successfully -- these statements might fail to execute and throw exceptions. Writing data to specific connectors can be considered as one type of such execution exception.

[hantangwangd]

Additionally, when starting a transaction, it's impossible to know in advance which connector will be written to, and thus whether the write operation is supported.

[phillipkoza]

Ok, So you are saying that Presto "supports" transactions because you can call commit and rollback? You say its "commit or rollback is not a noop". Well, if every statement you try to execute fails with an exception, then the commit or rollback is basically a no-op because there is nothing to commit or rollback. Note that if the underlying database doesn't support txns, then none of the statements will succeed, so the txn will always be a no-op at best. And probably if you call commit or rollback on a database that doesn't support txns, that will get an exception too. So you are saying: "we support txns for Iceberg even though you can't execute any operations inside of them". That's likely saying you support TLS 1.3 but you can't actually send any data with TLS1.3. You are basically saying that Presto is entitled to say it supports txns even though in many/most cases it does not and can not. Presto really shouldn't be saying it supports transactions in all cases if it is not true. Don't see why it would be worse to have Presto say it doesn't support txns. If a client is using a connector that allows it, it will work and they can just ignore that supportsTransactions returns false. Seems better to me then having it say it does and then trusting that and then at execution time dying with an error.

[hantangwangd]

So grateful for sharing your perspective, it reveals where users might struggle.

But if we examine the comments for the supportsTransactions() method, it doesn't strictly require that returning true must guarantee transaction support under all or most of circumstances or across all storage engines. However, it explicitly defines the behavior when false is returned, that is: invoking the method commit is a noop, and the isolation level is TRANSACTION_NONE. This is clearly not the case in Presto, so it seems that we shouldn't return false under the guidance of this API method comment.

A relevant example is MySQL -- its MyISAM storage engine does not support transactions, and moreover, nearly all of its built-in storage engines (other than InnoDB) lack transaction support. Yet MySQL's supportsTransactions() method always returns true directly.

Following your perspective, perhaps we should supplement the transaction support documentation by clearly outlining the current limitations -- or refine the exception messages to make users more explicitly aware of Presto's constraints on transactional support. Just as no MySQL user would expect to perform transactional operations with MySIAM engine...