[jira] [Comment Edited] (PHOENIX-2606) Cursor support in Phoenix

["Ankit Singhal (JIRA)" <ji...@apache.org>]

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

Ankit Singhal edited comment on PHOENIX-2606 at 1/29/16 1:36 PM:
-----------------------------------------------------------------

AFAIK , cursor are mostly used with store procedures where a database developer want to iterate over a result of a query with the use of cursor to do some other dependent sql operations.
By supporting cursor do we mean that we need to start supporting store procedures with all the SQL programming constructs? 

are there any use-cases of cursor with programming language other than database programming?

RVC may be the best to go if user doesn't want to cache the millions of results in one go and want pagination or "want more" type of support. 
But I think we cannot use RVC type support for pagination every time like it's not optimal to use with aggregate queries and tables/indexes where the whole primary key is not known or not a part of the query. correct me [~jamestaylor] if I am wrong here.

And, Please don't mind if my knowledge about cursors is limited. 


was (Author: ankit.singhal):

AFAIK , cursor are mostly used with store procedures where a database developer want to iterate over a result of a query with the use of cursor to do some other dependent sql operations.
By supporting cursor do we mean that we need to start supporting store procedures with all the SQL programming constructs? 

are there any use-cases of cursor with programming language other than database programming?

RVC may be the best to go if user doesn't want to cache the millions of results in one go and want pagination or "want more" type of support. 
But I think we cannot use RVC type support for pagination every time like it's not optimal to use with aggregate queries and tables/indexes where the whole primary key is not known or not a part of the query. correct me @james if I am wrong here.

And, Please don't mind if my knowledge about cursors is limited. 

> Cursor support in Phoenix
> -------------------------
>
>                 Key: PHOENIX-2606
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2606
>             Project: Phoenix
>          Issue Type: New Feature
>            Reporter: Sudarshan Kadambi
>
> Phoenix should look to support a cursor model where the user could set the fetch size to limit the number of rows that are fetched in each batch. Each batch of result rows would be accompanied by a flag indicating if there are more rows to be fetched for a given query or not. 
> The state management for the cursor could be done in the client side or server side (i.e. HBase, not the Query Server). The client side state management could involve capturing the last key in the batch and using that as the start key for the subsequent scan operation. The downside of this model is that if there were any intervening inserts or deletes in the result set of the query, backtracking on the cursor would reflect these additional rows (consider a page down, followed by a page up showing a different set of result rows). Similarly, if the cursor is defined over the results of a join or an aggregation, these operations would need to be performed again when the next batch of result rows are to be fetched. 
> So an alternate approach could be to manage the state server side, wherein there is a query context area in the Regionservers (or, maybe just a temporary table) and the cursor results are fetched from there. This ensures that the cursor has snapshot isolation semantics. I think both models make sense but it might make sense to start with the state management completely on the client side.



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