Guide to PostgreSQL Cursors

A dive into how cursors work in PostgreSQL.

A database cursor is a pointer that allows the user to iterate over the query result in a row-based order. Typically, cursors can be defined with different properties: “read-only” vs “updatable”, “forward-only” vs “scrollable”, and “sensitive” vs “insensitive”. We will discuss these properties with respect to PostgreSQL and how they work internally.

A cursor can be defined using the DECLARE clause with the following syntax:

DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query

The query is first parsed into a query tree by the planner/optimizer. The executor will then process the query tree and return the results incrementally, as described here.

Every time a FETCH command is issued, the executor will run the top node of the query tree once. It will recursively run its descendants and, eventually, the top node will return the next row of the result set. This process can be done iteratively until the query result is exhausted and the top node returns NULL. (Note: This process is handled by the ExecutionRun() procedure defined in “execMain.c” of the source file.)

Sensitivity

Cursor sensitivity as defined in the SQL:2016 standard:

If a cursor is open, and the SQL-transaction in which the cursor
was opened makes a significant change to SQL-data, then whether
that change is visible through that cursor before it is closed is
determined as follows:

— If the cursor is insensitive, then significant changes are not
visible.
— If the cursor is sensitive, then significant changes are
visible.
— If the cursor is asensitive, then the visibility of significant
changes is implementation-dependent.

At the time of this writing, PostgreSQL only supports insensitive cursors. This means that changes to the original table(s), made within the same transaction, after the cursor has been declared will not be visible. Thus, specifying ASENSITIVE or INSENSITIVE has no effect.

Indeed, a snapshot of the table(s) will be created when the cursor is opened (i.e. when it is declared). During a Fetch, the cursor portal will load the snapshot every time before the executor is called. It will only pop the snapshot after the executor has finished processing. (Note: This can be seen from SPI_cursor_open_internal()and PortalRunSelect().)

Updatability

Cursors can be made updatable by adding a FOR UPDATE or FOR SHARE at the end of the query. This will give the SELECT statement an exclusive or shared lock, respectively, from other concurrent transactions when updating/reading a row. They are useful when we want to update or delete rows in the original table(s) through the cursor using {UPDATE | DELETE} … WHERE CURRENT OF <cursor_name>.

Keep in mind that cursor updatability is slightly different from the SQL:2014 standard. In PostgreSQL, any update/deletion on the original table(s), even those through insensitive cursors made using {UPDATE | DELETE} … WHERE CURRENT OF <cursor_name> statements, will not be visible in that same cursor. This is because the snapshot was made when the cursor was opened, as described above, well before the changes can take place.

Scrollability

If the cursor is specified with SCROLL, it can be used to retrieve rows both in the forward direction (i.e. next row) and backward direction (i.e. previous row).

This means that the executor must be able to fetch rows in the backward direction. Depending on the query plan, a Materialize node may need to be attached on top of the query tree to support backward fetches. The Materialize node will store all of the fetched row(s) into a temporary storage called tuplestore, which allows the previous rows to be accessed again at a later time.

The tuplestore will store the fetched row(s) into an in-memory array if their collective size is below a specified value. Otherwise, it will store the row(s) into a temp file on the disk. Aside from enabling backward fetches, it also allowed for efficient random access within the query result, since it avoided the need for the executor to scan over large number of rows to reach a desired row.

When WITH HOLD is specified, the cursor can continue to be referenced and used after the transaction that created it has successfully committed. At the time the holdable cursor is declared, the query will be run to completion and the row(s) dumped into tuplestore. Every time a FETCH command is issued on the next transaction, it will retrieve a row from the tuplestore instead.

Internally, a new portal is created whenever a cursor is opened through DECLARE. A portal is simply an abstraction over the execution state of the cursor and will take on different execution strategies depending on the query. Some relevant ones are:

1. If the query contains a single SELECT statement, the executor will be run, and a new row will be returned every time the FETCH command is issued.
2. If the query contains a single INSERT/UPDATE/DELETE query with a RETURNING clause, the executor will perform the update/insert/delete to completion and dump the query result into the tuplestore. When the FETCH command is issued, the row will be read from the tuplestore.
3. If the query contains a utility statement (e.g. EXPLAIN or SHOW), it will run the statement to completion and dump the query result into the tuplestore. Similarly, row will be read from the tuplestore when FETCH is issued.

Referenced source code files:

• PostgreSQL Source Code: src/backend/executor/execMain.c Source File
• PostgreSQL Source Code: src/backend/executor/spi.c Source File
• PostgreSQL Source Code: src/include/utils/portal.h Source File
• PostgreSQL Source Code: src/backend/tcop/pquery.c Source File
• PostgreSQL Source Code: src/backend/utils/sort/tuplestore.c Source File