Do PostgreSQL cursors operate on snapshots?

Question

I declare a cursor in PostgreSQL, open it & start fetching data in batches.

Does the cursor operate on a "snapshot" (=copy) of the table made at the time of cursor opening, or is it susceptible to non-repeatable reads (or perhaps even dirty reads?).

E.g.

1. Cursor opens at timestamp T1=00:00:01.

2. First batch is read, all data corresponds to state T1.

3. In the meantime, another transaction committed changes to all data at timestamp T2=00:00:05.

4. Second batch is read, will the fetched data correspond to T1 or T2?

I know that many DB systems have their cursor operate on "immutable copies" rather than the actual table, but cannot find any information on how it's handled in PostgreSQL.

Answer 1

is it susceptible to non-repeatable reads

will the fetched data correspond to T1 or T2?

T1. Default isolation mode is read committed so two subsequent queries in a transaction can appear to read non-repeatably but this does not apply to cursors which read repeatably even when

• not used in repeatable read or serializable modes,
• the changes are done by the transaction they're in.

The doc immediately referenced by @Bergi addresses the latter case:

Cursor sensitivity determines whether changes to the data underlying the cursor, done in the same transaction, after the cursor has been declared, are visible in the cursor. INSENSITIVE means they are not visible (…) In PostgreSQL, all cursors are insensitive

The demo at the end covers both. Other commands don't read repeatably in the 2nd case.

---

(or perhaps even dirty reads?).

Postgres doesn't Dirty Read:

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

An admin can use extensions or intentionally corrupt the db to introduce them, but this universally applies to all software.

---

Does the cursor operate on a "snapshot"

Yes, in PostgreSQL MVCC everything^™ does.

---

"snapshot" (=copy) of the table

pg_current_snapshot() shows what's (in) an MVCC snapshot: a bunch of identifiers corresponding to row versions, not table copies.
Saying "I'm using snapshot 3" doesn't mean you clone any data version 3 to keep it secure, only that the db will interact with you as if versions 1&2 don't exist anymore, 4 and above don't exist yet, regardless of whether it physically has them. It'll also keep v3's around until everyone's done with them.

---

_{demo at db<>fiddle}

create table t(id)as select 1;
create extension dblink;
select dblink_connect('another_session','');
begin isolation level read committed;
declare c cursor for select*from t;

At this stage, a regular select and a cursor based on one, both see the same thing.
Now, while this transaction is still in progress, another session commits a change on this table. Since we're in read committed mode, a select will pick up the change:

select dblink('another_session','begin;update t set id=2;commit;');
select*from t;

id
2

The cursor will not, still seeing the state from back when it was opened, regardless of the isolation mode:

fetch first from c;

id
1

Even if the change originates from the current transaction, same one that operates this cursor, the cursor still reads repeatably, as if nothing happened. Even if you used that very cursor for it:

update t set id=-9 where current of c;
fetch first from c;

id
1

Answer 2

A PostgreSQL cursor operates on a stable snapshot, but that does not necessarily mean that it materializes (creates a copy of) the result set.

PostgreSQL implements a multiversion design that can keep several versions of a row in the table. Each row version “knows” when it was created and replaced. When a query starts or a cursor starts processing, PostgreSQL takes a snapshot of the database, which is essentially a description of the transactions that are visible. Taking a snapshot doesn't copy any data, it just determines which version of a row will be visible. The VACUUM process that performs garbage collection of obsolete row versions will not remove row versions needed by an active snapshot.

As a consequence, a cursor will see the same state of the database for its entire lifetime, regardless of concurrent data modifications.

Note that cursors normally exist only within a database transaction, which is why you shouldn't hold cursors open for too long. You can work around that by declaring a cursor WITH HOLD — but such cursors do materialize the result set when the transaction that created them commits. Therefore, it is important to CLOSE such cursors when you are done, so that the materialized result set can be freed.