Re: libpq: How are result sets fetched behind the scene?

Christian Barthel <bch(at)online(dot)de> writes:
> Sebastien FLAESCH <sf(at)4js(dot)com> writes:
>> Is the whole result set fetched to the client app, not matter what row
>> number is provided to the first PQgetvalue() call (or similar API call
>> on result set data or meta-data)?

> I have tested this as well and came to the same result as you.
> The entire result set seems to be fetched at once.

This must be so, and is documented as being so, because the abstraction
that libpq provides is that a query either succeeds or fails. It cannot
fetch a few rows and then decide that the query has succeeded; it has to
collect the whole input before it knows there will be no late failure.

Recent releases of libpq have an API to let you examine the rows as
they come in, but then it's on your head to deal with the situation
where an error occurs after you've already processed some rows.
See

https://www.postgresql.org/docs/current/libpq-single-row-mode.html

You can alternatively use a cursor and FETCH a few rows at a time,
as you mentioned. An error later than the first FETCH is still
possible that way, but it's quantized in some sense --- any one
FETCH either succeeds or fails.

regards, tom lane