2

This is not very clear to me as when we use a cursor on a very large table does pg (server) scan all the records loads them in a temporary waiting space until the client starts consuming it ? or if PG holds/pauses physical scan of all records until client has asked for next rows?

If its the second one then, does it scan on the snapshot of the table at the time when cursor was created so CRUD operations don't interfere? (is it then safe to say that cursor can be used to paginate the results without the need for order by clause as its working on point in time snapshot of the table and nothing can change?)

Also what happens when group by clause is added to a cursor query i assume in such a case it will be forced to scan all records on the tables save them in temp and return from it in batches?

The question is for whats the right way to paginate and read huge data table.

Improve this question

1 Answer 1

Reset to default
0

If the cursor is declared WITH HOLD, then it will copy the data to temporary storage. Otherwise, it might or might not, depending on what kind of plan it comes up with and maybe what other options are specified for the cursor. In either case a consistent snapshot will be used.

In the case of GROUP BY, only the result rows would need to be saved, but perhaps scratch space used by the sorting or the hash tables in order to do the grouping would not be released until the cursor is closed.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This does mean that cursors carry state, while in search of answer, i came across a mathematical answer for partitioning the result set do you think this is accurate and will work in real life? linkedin.com/pulse/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.