Cockroach: sql: add support for cursors

[ghost]

When using other databases, such as Oracle/PG, I can process data row by row through a cursor, but CockroachDB does not support it. Can you tell me which version is supported?

[awoods187]

Hi ethanhexd, we don't support cursors yet. I understand that you are asking about cursors in relation to pagination. In our engineer's experience, cursors are generally not a great architecture solution as they force the server to keep state. Server pagination does not scale well and cursors are rarely recommended even if the feature itself is implemented.

We actually recommended doing “client-side pagination” by retrieving a set of records with a limit, and then checking the index key of the last row, using that as an index constraint, and then running the query again.

This solution isn't specific to Cockroach but is actually a best practice recommendation for all databases. The reason is that offset has to get the same data as before, just skips the first N. To address this, you have to participate as a client by remembering the index key of the last result set you saw.

Let me know if you have further questions and I hope this helps!

[ghost]

Thank you for your reply!
"We actually recommended doing “client-side pagination” by retrieving a set of records with a limit, and then checking the index key of the last row, using that as an index constraint, and then running the query again." means the table must has primary key or index,there will be new problem:
Is it possible to retrieve a set of records with a limit without a primary key or index?

Unfortunately, some tables in our "Data Module" do not have primary key or index.

[knz]

All tables without an explicit primary key in CockroachDB automatically get an implicit (hidden) primary key named rowid.

You can use rowid to control the pagination, for example SELECT *, rowid FROM ... WHERE rowid > last_rowid_value

Would this help?

[ghost]

If CockroachDB don't support cursors, we will have a lot of resistance for application developers to use CockroachDB.
Application cannot gradually move to CockroachDB...

[knz]

The following page is a good starting point to explain the situation around pagination:
https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

[RoachietheSupportRoach]

Zendesk ticket #4710 has been linked to this issue.

[jordanlewis]

We have pgwire cursors now, does that help? We still don't have SQL-level DECLARE. We also have some duplicate issues around this. I'll close in favor of #41412.

[knz]

you'll need to update references to this issue in at least pkg/cmd/roachtest/psycopg_blocklist.go and perhaps other places

[jordanlewis]

D'oh, it wasn't marked as anchored telemetry... I'll re-open this and hide these latest comments :P

[knz]

I checked, it is not anchored telemetry. IT's not referred to from Go code .You were fine.

[knz]

(The references are from the orm test suite.)