[PHOENIX-7398] New PhoenixStatement API to return row for Atomic/Conditional Upserts

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 5.3.0
• Assignee: Viraj Jasani
• Reporter: Viraj Jasani
• Created: 30/Aug/24 06:21
• Updated: 14/Nov/24 09:12
• Resolved: 11/Sep/24 00:14

Description

Phoenix supports Atomic Conditional Upserts with ON DUPLICATE KEY clause, allowing clients to either ignore the update if the row with the given primary key(s) already exist or conditionally update the row with the given primary key(s). Phoenix also supports returning 0 or 1 as updated status code for the given Atomic/Conditional Upserts, where 0 represents no updates to the row and 1 represents the updated row (when the condition is satisfied by the atomic upsert).

While standard JDBC APIs support Upserts with the status code returned as integer values 0 or 1, some client applications also require returning the current row state back to the client depending on whether the row is successfully updated. If the condition is satisfied by the atomic upsert and the row is updated, the client application should be able to get the updated row status as HBase Result object. Similarly, if the condition is not satisfied for the given atomic upsert and therefore, row is not updated, the client application should be able to get the old/current row status as HBase Result object.

This support is somewhat similar in nature to what HBase provides for Increment and Append mutations. Both operations return updated row as Result object. For instance,

/**
 * Appends values to one or more columns within a single row.
 * <p>
 * This operation guaranteed atomicity to readers. Appends are done under a single row lock, so
 * write operations to a row are synchronized, and readers are guaranteed to see this operation
 * fully completed.
 * @param append object that specifies the columns and values to be appended
 * @throws IOException e
 * @return values of columns after the append operation (maybe null)
 */
default Result append(final Append append) throws IOException {

 

HBase does not provide API to return row state for atomic updates with Put and Delete mutations. HBase API checkAndMutate() also supports returning Result for Append and Increment mutations only, not for Put and Delete mutations.

Phoenix supports batchMutate() with Put mutation(s) by making it Atomic. Hence, for client applications using Phoenix provided atomic upserts, it would be really beneficial to also provide new API at PhoenixStatement and PhoenixPreparedStatement layer that can return the row as Result object back to the client.

The proposed API signature:

/**
 * Executes the given SQL statement similar to JDBC API executeUpdate() but also returns the
 * updated or non-updated row as Result object back to the client. This must be used with
 * auto-commit Connection. This makes the operation atomic.
 * If the row is successfully updated, return the updated row, otherwise if the row
 * cannot be updated, return non-updated row.
 *
 * @param sql The SQL DML statement, UPSERT or DELETE for Phoenix.
 * @return The pair of int and Tuple, where int represents value 1 for successful row
 * update and 0 for non-successful row update, and Tuple represents the state of the row.
 * @throws SQLException If the statement cannot be executed.
 */
public Pair<Integer, Tuple> executeUpdateReturnRow(String sql) throws SQLException {

Issue Links

• relates to: PHOENIX-7411 Atomic Delete: PhoenixStatement API to return row if single row is atomically deleted
• links to: GitHub Pull Request #1967