[jira] [Comment Edited] (PHOENIX-2909) Surface checkAndPut through UPDATE statement

["Cameron Hatfield (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435680#comment-15435680 ] 

Cameron Hatfield edited comment on PHOENIX-2909 at 8/24/16 9:04 PM:
--------------------------------------------------------------------

I like the idea of this (of course), but the issue I have is one that this doesn't support our use case because it requires an existing row, unlike an upsert statement. Also seems like it starts getting confusing when you have two separate statements, that are essentially doing the same thing, but have a slightly different use case due to the purely backend change to it flushing the memstore. I would expect an UPDATE statement to behave exactly the same as an UPSERT, in the case that a row already exists. But violating the principle of least surprise, it actually ends up being more expensive, with little to no warning to the user aside from a note in the documentation.

Wouldn't this be better to add as a flag / etc on top of the existing upsert statement? Should we reconsider the work already done on PHOENIX-2271?


was (Author: cameron.hatfield):
I like the idea of this (of course), but the issue I have is one that this doesn't support our use case because it requires an existing row, unlike an upsert statement. Also seems like it starts getting confusing when you have two separate statements, that are essentially doing the same thing, but have a slightly different use case due to the purely backend change to it flushing the memstore. I would expect an UPDATE statement to behave exactly the same as an UPSERT, in the case that it already exists. But violating the principle of least surprise, it actually ends up being more expensive, with little to no warning to the user aside from a note in the documentation.

Wouldn't this be better to add as a flag / etc on top of the existing upsert statement? Should we reconsider the work already done on PHOENIX-2271?

> Surface checkAndPut through UPDATE statement
> --------------------------------------------
>
>                 Key: PHOENIX-2909
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2909
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: James Taylor
>            Assignee: James Taylor
>             Fix For: 4.9.0
>
>
> We can surface atomic checkAndPut like functionality through support of the SQL UPSERT statement.
> For example, the following could use do a get under row lock to perform the row update atomically
> {code}
> UPDATE  my_table SET counter=coalesce(counter,0) + 1 
> FROM my_table WHERE pk1 = 1 AND pk2 = 2;
> {code}
> To force prior MVCC transactions to complete (making it serializable as an Increment is), we'd have code like this:
> {code}
>     mvcc = region.getMVCC();
>     mvcc.completeMemstoreInsert(mvcc.beginMemstoreInsert());
> {code}
> By users setting auto commit to true and issuing an UPDATE statement over a non transactional table, they'd get a way for row updates to be atomic. This would work especially well to support counters.
> An UPDATE statement would simply be translated to an equivalent UPSERT SELECT with a flag being passed to the server such that the row lock and read occurs when executed. For example, the above statement would become:
> {code}
> UPSERT INTO  my_table(pk1,pk2,counter) SELECT pk1, pk2, coalesce(counter,0) + 1 
> FROM my_table WHERE pk1 = 1 AND pk2 = 2;
> {code}
> Note that the coalesce call above handles the case where counter is null. This could be made prettier with support for the DEFAULT clause at CREATE TABLE time (PHOENIX-476).



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)