Re: C libpq frontend library fetchsize

Takahiro Itagaki wrote:
> Yeb Havinga <yebhavinga(at)gmail(dot)com> wrote
>> I'm wondering if there would be community support for adding using the
>> execute message with a rownum > 0 in the c libpq client library, as it
>> is used by the jdbc driver with setFetchSize.
>>
>
> The setFetchSize for libpq is difficult because of the interface
> mismatch -- libpq uses array-based APIs (PGresult) and JDBC uses a
> cursor-like API (ResultSet). Instead, you can use CURSOR and FETCH
> commands to retrieve rows in separated PGresult objects.
>
Hello Takahiro,

Thank you for your reply. Yes there is a large overlap with SQL's
declare cursor together with fetch, however intuitively it feels strange
that the application needs to be changed for something that could be
kept internal to the communication protocol. The application is forced
to start an application explicitly, and also I tried to declare a cursor
with parameters from sql but did not succeed, nor could I declare a
cursors in combination with a prepared statement. Building fetchsize
into libpq seems like a general solution that could work for all
pgresult fetching.

Together with a college of mine I worked on this subject some time ago,
and we thought it might be interesting enough for others as well. In
short this is what we did:
- add a boolean 'complete' to the PGresult object - to indicate if the
portalrun was complete
- add PQresultComplete api call that returns the boolean above.
- add PQsendSync
- in the query guts routines, do not send sync messages
- call pqsendsync when a complete pqresult is received, or if result
fetching is stopped before fetching the last result.
- the fetchsize was a defined constant in our application, but would be
trivial to replace with a PQsetFetchsize.

There are some intricacies with reading with libpq with an asynchronous
event handler. One was that parseinput does not 'eat' the whole message
buffer, i.e. there can be another complete message in the buffer but a
new read event might not be triggered because no more data arrives into
the buffer. For this purpose we also added
- PQmsgAvail - returns true if the event handler may fire again
immediately (to process more results)

The biggest challenge was when to send sync messages. We have that
covered currently but it is done by our application outside of libpq
(hence the pqsendsync visible in the api as well as pqresultcomplete).
It would be better if that all could be kept inside libpq itself. In the
end we believe this could provide useful functions for callers of the
libpq api such as PHP.

regards,
Yeb Havinga