[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15530934#comment-15530934
]
James Taylor commented on PHOENIX-2909:
---
This solution isn't complete because it doesn't handle the potential race
between a row being inserted and a row being updated. Instead, the plan is to
implement PHOENIX-6. I think this addresses your concern as well,
[~cameron.hatfield].
> 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
>
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15437596#comment-15437596
]
Andrew Purtell commented on PHOENIX-2909:
-
You might consider implementing counters as simple puts of deltas as Increments
with SELECT time aggregation of deltas into the current value rather than use
checkAndPut. The semantics won't be quite the same but could satisfy the
majority of counter use cases and my intuition says that will perform much
better on writes and competitively on reads.
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435991#comment-15435991
]
James Taylor commented on PHOENIX-2909:
---
bq. How does this update statement interact with transaction support?
It wouldn't impact transactional tables since they have an optimistic
concurrency model for conflict detection. This JIRA is more about surfacing a
simple model for things like counters where you may not need full blown
transactions. It'll be interesting to measure the perf of the equivalent
counter code with transactions versus w/out (using this new mechanims).
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435860#comment-15435860
]
Cameron Hatfield commented on PHOENIX-2909:
---
I would agree, and I would say is a good argument for having something else
within the merge statement call out the use of whether this will be a fast /
slow version. My basic worry is that when I see something like UPDATE vs UPSERT
or INSERT vs INSERT ... ON DUPLICATE KEY IGNORE, I always expect the more
complicated / does more for me statement to be either the same speed or slower
in the general case. With the proposed change, the more constrained case UPDATE
is actually slower. Whether that is a valid concern is up to debate.
How does this update statement interact with transaction support?
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435728#comment-15435728
]
James Taylor commented on PHOENIX-2909:
---
Wouldn't we be in the same boat with MERGE statement proposed for PHOENIX-2275
in that MERGE would take the perf hit while UPSERT wouldn't (with a doc note
explaining why)? As far as PHOENIX-2271, we want to stay away from non ANSI SQL.
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435680#comment-15435680
]
Cameron Hatfield commented on PHOENIX-2909:
---
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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435639#comment-15435639
]
James Taylor commented on PHOENIX-2909:
---
The two aren't mutually exclusive. We can still surface this functionality when
calcite comes online through a MERGE command. It's important that we have a non
checkAndPut code path, though (through UPSERT) so that users who don't need
this functionality (for better perf). Another mitigating factor is that we can
get this into Phoenix prior to calcite makes it into master.
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435629#comment-15435629
]
Cameron Hatfield commented on PHOENIX-2909:
---
What has changed since the original discussion that no longer makes
PHOENIX-2275 the correct answer?
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435628#comment-15435628
]
Cameron Hatfield commented on PHOENIX-2909:
---
Previous discussion on check-and-put
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15426715#comment-15426715
]
Josh Mahonin commented on PHOENIX-2909:
---
$DAYJOB would also be very interested in this feature!
> 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)
[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15426683#comment-15426683
]
James Taylor commented on PHOENIX-2909:
---
FYI, [~gho...@salesforce.com] - keep your eye on this for atomic counter
support.
> 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
>
> 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)
