[
https://issues.apache.org/jira/browse/PHOENIX-2271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14905383#comment-14905383
]
Cameron Hatfield commented on PHOENIX-2271:
-------------------------------------------
For the issue of trying to automatically determine when to do what for MERGE:
The main issue I see is that transactions and compare-and-set offer similar,
but subtly different concurrency guarantees,
that I believe are impossible to automatically choose for a user.
Specifically, imagine the following example:
CREATE TABLE MyTable (id INT primary key, value INT)
UPSERT INTO MyTable (id, value) values(1, 1)
Now, you have merge statement A:
MERGE INTO MyTable
USING (VALUES (1, 2)) AS NewData (id, value)
ON MyTable.id = NewData.id and value >= 1
WHEN MATCHED THEN UPDATE SET MyTable.value = MyTable.value
WHEN NOT MATCHED THEN INSERT VALUES (NewData.id, NewData.value)
And merge statement B
MERGE INTO MyTable
USING (VALUES (1, 3)) AS NewData (id, value)
ON MyTable.id = NewData.id and value >= 1
WHEN MATCHED THEN UPDATE SET MyTable.value = MyTable.value
WHEN NOT MATCHED THEN INSERT VALUES (NewData.id, NewData.value)
The main difference between the two is that value is 2 in the first, and value
is 3 in the other.
Now what happens if they conflict?
In a compare-and-set world, the last one to commit will win (since the
condition will match, no matter the order). No exception will ever be thrown.
But in a transaction world, the first one to commit will win, as a transaction
error would be thrown when the second one is committed.
So the options seem to become either adding a special option to merge, that
allows you to force compare-and-set like functionality, which may mean the
grammar has some cases
where it has features that are not supported for compare-and-set vs a
transaction based merged, as well as having two slightly different MERGE
statements with the only difference being a single keywords, with two subtly
different semantics; or use the already non-standard UPSERT command as to add
that support, with a syntax that calls out that this is a different operation.
This would
also help differentiate UPSERT from MERGE, allowing you to do more low-level /
different types of concurrency, as otherwise UPSERT becomes essentially a
subset of the merge operation.
> Upsert - CheckAndPut like functionality
> ---------------------------------------
>
> Key: PHOENIX-2271
> URL: https://issues.apache.org/jira/browse/PHOENIX-2271
> Project: Phoenix
> Issue Type: Improvement
> Reporter: Babar Tareen
> Attachments: patch.diff
>
>
> The Upsert statement does not support HBase's checkAndPut api, thus making it
> difficult to conditionally update a row. Based on the comments from
> PHOENIX-6, I have implemented such functionality. The Upsert statement is
> modified to support compare clause, which allows us to pass in an expression.
> The expression is evaluated against the current record and Upsert is only
> performed when the expression evaluates to true. More details
> [here|https://github.com/babartareen/phoenix].
> h4. Examples
> Given that the FirstName is always set for the users, create a user record if
> one doesn't already exist.
> {code:sql}
> UPSERT INTO User (UserId, FirstName, LastName, Phone, Address, PIN) VALUES
> (1, 'Alice', 'A', '123 456 7890', 'Some St. in a city', 1122) COMPARE
> FirstName IS NULL;
> {code}
> Update the phone number for UserId '1' if the FirstName is set. Given that
> the FirstName is always set for the users, this will only update the record
> if it already exists.
> {code:sql}
> UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName
> IS NOT NULL;
> {code}
> Update the phone number if the first name for UserId '1' starts with 'Al' and
> last name is 'A'
> {code:sql}
> UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName
> LIKE 'Al%' AND LastName = 'A';
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
