Cursors with postgres, where is the data stored and how many calls to the DB

Question

Hi I am using psycopg2 for postgres access.

I am trying to understand where "cursor" stores the returned rows. Does it store it in the database as a temporary table or is it on the clients end?

Does cursor (when you specify to fetch many rows) hit the database a query at a time or does it hit the database once ,get the first set of results then when you iterate over the returned values, it gets the next set (buffering).

I have read multiple articles on cursor but nothing really gives the inner working...

Thank you.

Answer 1

The dataset for a cursor is prepared by the server at the time of execution of the first FETCH. The client application receives only the results of subsequent FETCH statements.

If the server cannot use indexes to maintain a cursor, the temporary dataset is created. You can perform this simple test:

create table test(i int, v text);
insert into test
select i, i::text
from generate_series(1, 5000000) i;

Execute the statements in this script one by one:

begin;

declare cur cursor 
for select * from test
order by random();             -- 17 ms

fetch next cur;                -- 37294 ms (*)

fetch next cur;                -- 0 ms
fetch prior cur;               -- 0 ms
fetch absolute 1000000 cur;    -- 181 ms
fetch relative 1000000 cur;    -- 163 ms
fetch first cur;               -- 0 ms
fetch last cur;                -- 0 ms

rollback;

First FETCH (*) performs roughly around the same time as the creation of a similar temporary table:

create temp table temp_test as
select * from test
order by random();             -- 51684 ms

Some drivers may have their own implementation of cursor on client side. This should be explicitly described in the driver's documentation.