Add DELETE and UPDATE to trino-base-jdbc

[lhofhansl]

Looking at the code involves something like the following:

• use getPageSourceProvider instead of getRecordSetProvider in JdbcConnector and return an UpdatablePageSource (probably a class that extends RecordPageSource and wraps the existing ResultSet)
• Implement the UpdatablePageSource methods (updateRows, deleteRows, etc)
• Figure out the RowId column handle - for JDBC just the PK columns
• Add getUpdateRowIdColumnHandle and getDeleteRowIdColumnHandle to JdbcMetadata
• Add buildDeleteSql and buildUpdateSql to JdbcClient and use those from the UpdatablePageSource to perform the writes.
• (And probably a bunch of more stuff...)

Once it's added to trino-base-jdbc it can be add the the Postgres, MySQL, Phoenix, etc, connectors.

Note sure I'll find time to do this, filing in case someones else is interested. :)

Relates to #5275

[findepi]

There is a PR for DELETE, using a different approach than outlined here.
#6287

[kokosing]

I think we can also leverage same approach as in #6287 for UPDATE.

Today I am not sure if support for arbitrary UPDATE and DELETE is very needed for JDBC connectors. WDYT?

[lhofhansl]

I was searching here for UPDATE (forgot to search for DELETE) my apologies.

I can't comment on whether it's generally important. Here we were debating whether we can use Trino as our (almost) general SQL frontend - since we have OLTP type data source (Oracle, PostgreSQL, Phoenix, etc) as well as OLAP (Hive and Iceberg tables). While there are other considerations as well, one counter point was that we cannot issue DELETEs or UPDATEs to our OLTP data sources. I'd argue it's just as important as adding DELETE and UPDATE to the Hive connector.

If #6287 is easier to do, let's do it that way. I was going by the added UpdatableDataSource and wanted to see how to leverage that.

[13250709979]

Looking at the code involves something like the following:

• use getPageSourceProvider instead of getRecordSetProvider in JdbcConnector and return an UpdatablePageSource (probably a class that extends RecordPageSource and wraps the existing ResultSet)
• Implement the UpdatablePageSource methods (updateRows, deleteRows, etc)
• Figure out the RowId column handle - for JDBC just the PK columns
• Add getUpdateRowIdColumnHandle and getDeleteRowIdColumnHandle to JdbcMetadata
• Add buildDeleteSql and buildUpdateSql to JdbcClient and use those from the UpdatablePageSource to perform the writes.
• (And probably a bunch of more stuff...)

Once it's added to trino-base-jdbc it can be add the the Postgres, MySQL, Phoenix, etc, connectors.

Note sure I'll find time to do this, filing in case someones else is interested. :)

Relates to #5275
Recently, I want to implement the delete and update statements of JDBC. Can you provide a more detailed modification scheme? thank you

[kokosing]

@lhofhansl Since DELETE is merged, would you be willing to work on UPDATE?

[findepi]

per

trino/plugin/trino-base-jdbc/src/main/java/io/trino/plugin/jdbc/DefaultJdbcMetadata.java

Line 680 in 825e6d8

throw new TrinoException(NOT_SUPPORTED, "Unsupported delete");

I think it would be valuable to first extend DELETE to support cases when the delete predicate cannot be fully pushed down (eg LIKE, subquery, a function call, something requiring coercions, etc.)

[Jackhuihui]

@lhofhansl Since DELETE is merged, would you be willing to work on UPDATE?

UPDATE to trino-base-jdbc Still not supported，I think its a common use function @lhofhansl

[kokosing]

There is work already in progress for UPDATE, see #16445

CC: @vlad-lyutenko