Skip site navigation (1) Skip section navigation (2)

Re: C libpq frontend library fetchsize

From: Yeb Havinga <yebhavinga(at)gmail(dot)com>
To: Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org, Willem Dijkstra <wpd(at)xs4all(dot)nl>
Subject: Re: C libpq frontend library fetchsize
Date: 2010-03-01 09:55:46
Message-ID: 4B8B8F22.8040100@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2010-03-01 11:00:15
Subject: Re: [GENERAL] trouble with to_char('L')
Previous:From: Andrea SuisaniDate: 2010-03-01 09:13:42
Subject: Re: Testing of parallel restore with current snapshot

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group