A cursor is a placeholder into a selection query that allows you to iterate over subsets of the rows returned by that query.
This document describes cursor usage within SQL transactions. For information about using cursors in PL/pgSQL functions and procedures, see PL/pgSQL.
Cursors differ from keyset pagination and LIMIT
/OFFSET
in that:
- Each cursor is a stateful SQL object that is referred to by a unique name.
- Each cursor requires holding open its own dedicated (read-only) transaction.
- Each cursor operates on a snapshot of the database at the moment that cursor is opened.
Synopsis
Cursors are declared and used with the following keywords:
Examples
These examples assume the presence of the MovR data set.
Use a cursor
BEGIN;
DECLARE rides_cursor CURSOR FOR SELECT * FROM movr.rides;
FETCH FORWARD 5 FROM rides_cursor;
id | city | vehicle_city | rider_id | vehicle_id | start_address | end_address | start_time | end_time | revenue
---------------------------------------+---------------+---------------+--------------------------------------+--------------------------------------+-----------------------------+----------------------------+---------------------+---------------------+----------
8ccccccc-cccc-4000-8000-000000000113 | san francisco | san francisco | 80000000-0000-4000-8000-000000000019 | 77777777-7777-4800-8000-000000000007 | 69313 Jody Tunnel Apt. 17 | 5210 Kim Canyon Suite 84 | 2018-12-22 03:04:05 | 2018-12-22 07:04:05 | 36.00
8d4fdf3b-645a-4000-8000-000000000114 | san francisco | san francisco | 80000000-0000-4000-8000-000000000019 | 88888888-8888-4800-8000-000000000008 | 54797 Lauren Cliffs Apt. 37 | 7425 Matthews Harbors | 2018-12-18 03:04:05 | 2018-12-20 04:04:05 | 45.00
8dd2f1a9-fbe7-4000-8000-000000000115 | san francisco | san francisco | 75c28f5c-28f5-4400-8000-000000000017 | 77777777-7777-4800-8000-000000000007 | 23053 Brown Creek | 15838 Preston Unions | 2018-12-26 03:04:05 | 2018-12-27 15:04:05 | 34.00
55810624-dd2f-4c00-8000-0000000000a7 | seattle | seattle | 570a3d70-a3d7-4c00-8000-000000000011 | 55555555-5555-4400-8000-000000000005 | 78340 Ashley Common Apt. 4 | 19798 Riggs Spring | 2018-12-08 03:04:05 | 2018-12-10 06:04:05 | 13.00
56041893-74bc-4c00-8000-0000000000a8 | seattle | seattle | 570a3d70-a3d7-4c00-8000-000000000011 | 66666666-6666-4800-8000-000000000006 | 6431 Robert Forest | 83655 Michael Cape Apt. 94 | 2018-12-09 03:04:05 | 2018-12-09 14:04:05 | 48.00
(5 rows)
CLOSE rides_cursor;
COMMIT;
View all open cursors
SELECT * FROM pg_cursors;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
---------------+---------------------+-------------+-----------+---------------+--------------------------------
rides_cursor | SELECT * FROM rides | f | f | f | 2023-03-30 15:24:37.568054+00
(1 row)
Known limitations
CockroachDB implements SQL cursor support with the following limitations:
DECLARE
only supports forward cursors. Reverse cursors created withDECLARE SCROLL
are not supported. #77102FETCH
supports forward, relative, and absolute variants, but only for forward cursors. #77102BINARY CURSOR
, which returns data in the Postgres binary format, is not supported. #77099WITH HOLD
, which allows keeping a cursor open for longer than a transaction by writing its results into a buffer, is accepted as valid syntax within a single transaction but is not supported. It acts as a no-op and does not actually perform the function ofWITH HOLD
, which is to make the cursor live outside its parent transaction. Instead, if you are usingWITH HOLD
, you will be forced to close that cursor within the transaction it was created in. #77101This syntax is accepted (but does not have any effect):
BEGIN; DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM foo ORDER BY bar; CLOSE test_cur; COMMIT;
This syntax is not accepted, and will result in an error:
BEGIN; DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM foo ORDER BY bar; COMMIT; -- This will fail with an error because CLOSE test_cur was not called inside the transaction.
Scrollable cursor (also known as reverse
FETCH
) is not supported. #77102SELECT ... FOR UPDATE
with a cursor is not supported. #77103Respect for
SAVEPOINT
s is not supported. Cursor definitions do not disappear properly if rolled back to aSAVEPOINT
from before they were created. #77104
Differences between cursors and keyset pagination
Cursors are stateful objects that use more database resources than keyset pagination, since each cursor holds open a transaction. However, they are easier to use, and make it easier to get consistent results without having to write complex queries from your application logic. They do not require that the results be returned in a particular order (that is, you don't have to include an ORDER BY
clause), which makes them more flexible.
Keyset pagination queries are usually much faster than cursors since they order by indexed columns. However, in order to get that performance they require that you return results in some defined order that can be calculated by your application's queries. Because that ordering involves calculating the start/end point of pages of results based on an indexed key, they require more care to write correctly.