Feedback: Paginate Results - cursor support

[hand-crdb]

Steven Hand (hand-crdb) commented:

Page: https://cockroachlabs.com/docs/v22.1/pagination.html

What is the reason for your feedback?

[ ] Missing the information I need

[ ] Too complicated

[X] Out of date

[ ] Something is broken

[ ] Other

Additional details

This page says "CockroachDB does not have cursors." But initial cursor support is in 22.1 based on this GitHub issue comment from Feb. 27:

I'm closing this since initial DECLARE support will be in v22.1.

cockroachdb/cockroach#41412 (comment)

That initial work is described more in this GitHub issue:

This PR implements the SQL CURSOR methods DECLARE, FETCH and CLOSE using streaming internal executors.

cockroachdb/cockroach#74006

Here's a summary of remaining work on cursors (from GitHub issue #41412):
sql: support BINARY cursor #77099
Open

sql: support MOVE on cursors #77100
Closed

sql: support DECLARE cursor WITH HOLD #77101
Open

sql: support DECLARE SCROLL and reverse FETCH on cursors #77102
Open

sql: support FOR UPDATE on cursors #77103
Open

sql: respect SAVEPOINT for cursors #77104
Open

Jira Issue: DOC-6185

[hand-crdb]

Cursors are also listed as not supported on this page:
https://www.cockroachlabs.com/docs/stable/sql-feature-support.html

[exalate-issue-sync]

Theodore Hyman (theodore-hyman) commented:
customer Arrive logistics is reporting confusion saying they found GH pages where it implied we had some cursor support but our docs say we don’t support cursors. Unclear. Looks to me like support is half-built (See epic) but we still don’t fully support it. I’m confused so just trying to bump this Jira to have docs confirm current status of CURSOR support. Ref the EPIC here: https://cockroachlabs.atlassian.net/browse/CRDB-11397

[exalate-issue-sync]

shannonb (shannonbradshaw) commented:
Dikshant Adhikari can you weigh in here?

[exalate-issue-sync]

Theodore Hyman (theodore-hyman) commented:
Thanks Shannon. To my understanding our CURSOR support is so limited its probably best to just say it is UNSUPPORTED but a customer was digging around in our github and raised this question so thought it would be something to double check on internally. If customers may benefit from the limited support we do offer, might be good to publicize. Unsure. Ty

[exalate-issue-sync]

Dikshant Adhikari (dikshant) commented:
Cursor does indeed work, I tried out the example from here:
https://www.educba.com/cursors-in-postgresql/

What doesn’t work yet is WITH HOLD and a bunch of extra stuff captured in that ticket. It is perhaps worth coming up an example of CURSOR usage. Not sure why this was not documented, it appears this was added in 22.1. Thoughts Rafi Shamim Rima Deodhar ?

A basic example I modified from the doc above:
Create tables

{noformat}CREATE TABLE Employee ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), PRIMARY KEY (emp_name));
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'ABC', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'PQR', 'Pune', '1234567890');
INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone) VALUES (1, 'XYZ', 'Mumbai', '1234567890');{noformat}

Creator cursor:

{noformat}DECLARE test_cur CURSOR FOR SELECT * FROM Employee ORDER BY emp_id;{noformat}

Fetch from cursor:

{noformat}FETCH 1 FROM test_cur;
FETCH 2 FROM test_cur;{noformat}

Close cursor:

{noformat}CLOSE test_cur;{noformat}

WITH HOLD is valid syntax but it is only allowed so long as the cursor is closed within the transaction it is created in.:

{noformat}BEGIN;
DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM Employee ORDER BY emp_id;
CLOSE test_cur;
COMMIT;{noformat}

The reason WITH HOLD exists is to make the cursor live outside of a transaction but we don’t support that.

{noformat}BEGIN;
DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM Employee ORDER BY emp_id;
COMMIT; --this will fail with an error because CLOSE test_cur was not called inside the transaction.{noformat}

Hope this example helps.

[exalate-issue-sync]

Dikshant Adhikari (dikshant) commented:
I have a 1:1 with Richard this week, I’ll work with him on getting this documented.

[exalate-issue-sync]

Rafi Shamim (rafiss) commented:
here is the docs issue in the backlog https://cockroachlabs.atlassian.net/browse/DOC-2700

[exalate-issue-sync]

Rafi Shamim (rafiss) commented:
as well as https://cockroachlabs.atlassian.net/browse/DOC-2699

[exalate-issue-sync]

Richard Loveland (rmloveland) commented:
I have some WIP on this right now, since we are documenting portals I figured we should also update our cursor docs

here’s what I have so far, should be a PR soon: 1c779e8

I will update it with the information from Dikshant’s comment above as well

[exalate-issue-sync]

Richard Loveland (rmloveland) commented:
Rafi Shamim would you be able to help me find a reviewer for this PR that adds some basic docs on cursors?

#16751

I was gonna ask Jordan but I think his response time might be pretty delayed due to just switching to CC (even tho he wrote cockroachdb/cockroach#74006 ). But let me know if you still think he’s the best person to ask