Jdbc merge support

[chenjian2664]

Description

This is a follow up of https://github.com/trinodb/trino/pull/16693

It mainly implements several important interfaces in the https://trino.io/docs/current/develop/supporting-merge.html#connector-support-for-merge document:

• ConnectorMergeSink API. In storeMergedRows, the page data is classified into INSERT, UPDATE, DELETE three categories, and then call the corresponding Sink class to operate the corresponding Connector. INSERT data is processed through the existing JdbcPageSink. UPDATE and DELETE type data are modified through the primary keys. The implementation reuses the JdbcPageSink code and only changes the executed sql statement. The syntax used when deleting data using primary key constraints in each database may be different, so when creating the Sink class, mergeRowIdConjuncts will be obtained from BaseJdbcClient (BaseJdbcClient#buildMergeRowIdConjuncts)

• getMergeRowIdColumnHandle API. For connectors that support Merge, return a RowType containing all primary key column information, otherwise return as original.

• beginMerge API. Returns JdbcMergeTableHandle, which contains all columns that need to be scanned, (implemented in BaseJdbcClient#updatedScanColumnsForMerge), and the tableHandle of Insert.

• In the third commit, FTE needs to be supported, so the finishMerge API is also implemented. Write all insert+delete data to the temporary table first, and then update the target table.

Additional context and related issues

There are also some details in https://github.com/trinodb/trino/pull/16944/

Release notes

(x) Release notes are required, with the following suggested text:

# Base Jdbc
* Support SQL MERGE for base jdbc connectors ({issue}`16709`)
* Update Phoenix merge implementation by reusing base Jdbc merge implementation.
* Support update/merge for Ignite
* Support update/merge for Postgresql

[chenjian2664]

@kokosing I divide the https://github.com/trinodb/trino/pull/16944 into those 3 commits, each commit is mainly support merge in one connector(Phoenix, Ignite, Postgresql). Oracle connector will be added latter since it requires to modify some base module tests. I hope this pr is cleaner for your review.

[kokosing]

@wendigo @vlad-lyutenko @hashhar would you like to review this PR?

[chenjian2664]

@electrum Would you mind to have a look when you have sometime? thanks

[chenjian2664]

Postgresql tests failures due to OOM will be resolved https://github.com/trinodb/trino/pull/21127

[chenjian2664]

@hashhar @kokosing Gentle reminder.

[chenjian2664]

@hashhar @kokosing Would you mind to have a review now

[findepi]

Can you please expand description section of the PR with high level overview of approach taken? ie. how it works.

[chenjian2664]

@findepi @hashhar @kokosing PTAL

[github-actions[bot]]

This pull request has gone a while without any activity. Tagging the Trino developer relations team: @bitsondatadev @colebow @mosabua

[nizarhejazi]

Hey @chenjian2664, @mosabua, @findepi any update on this PR? We would love to use this feature w/ dbt-trino adapter.

[chenjian2664]

Hey @chenjian2664, @mosabua, @findepi any update on this PR? We would love to use this feature w/ dbt-trino adapter.

Waiting for the review

[findepi]

cc @djsstarburst @electrum for more MERGE support

[nizarhejazi]

Hey team, any update on the status of this PR?

[LovAsawa-Draup]

Would love to have merge support for postgresql connector, as Update with help of from query is not directly supported. Any timeline when this feature can go live

[mosabua]

We are trying to give this priority in the review and merge queue at the moment. We definitely plan on getting this in. Timing is to be determined though.

[cla-bot[bot]]

Thank you for your pull request and welcome to our community. We could not parse the GitHub identity of the following contributors: Jason. This is most likely caused by a git client misconfiguration; please make sure to:

1. check if your git client is configured with an email to sign commits git config --list | grep email
2. If not, set it up using git config --global user.email [email protected]
3. Make sure that the git commit email is configured in your GitHub account settings, see https://github.com/settings/emails

[shohamyamin]

@chenjian2664 Any update on that issue? And you know if the Oracle connector will also benefit from that? Merge in oracle connector would be very useful for me

[chenjian2664]

@shohamyamin for some reason, we move the working on https://github.com/trinodb/trino/pull/23034 now. The workaround is also fit for the Oracle connector.