Re: C libpq frontend library fetchsize

Hello list,

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.

kind regards,
Yeb Havinga

On Fri, Feb 26, 2010 at 3:28 PM, 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.

Not sure I follow what you're asking... what would the new/changed
function signature be?

...Robert

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.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

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

Robert Haas wrote:
> On Fri, Feb 26, 2010 at 3:28 PM, 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.
>>
>
> Not sure I follow what you're asking... what would the new/changed
> function signature be?
>
Hello Robert, list

I'm sorry I did not catch your reply until I searched in the archives on
libpq, I hope you are not offended. However I think the question is
answered somewhat in a reply I sent to Takahiro Itagaki, viz:
http://archives.postgresql.org/pgsql-hackers/2010-03/msg00015.php

The recent posting in PERFORM where someone compares mysql vs postgresql
speed is caused by libpq / whole pgresult as one time.
(http://archives.postgresql.org/pgsql-performance/2010-03/msg00228.php)

ISTM that using cursors and then fetch is not an adequate solution,
because 1) someone must realise that the pgresult object is
gathered/transfered under the hood of libpq completely before the first
row can be used by the application 2) the structure of the application
layer is altered to make use of partial results.

What if the default operation of e.g. php using libpq would be as
follows: set some default fetchsize (e.g. 1000 rows), then just issue
getrow. In the php pg handling, a function like getnextrow would wait
for the first pgresult with 1000 rows. Then if the pgresult is depleted
or almost depleted, request the next pgresult automatically. I see a lot
of benefits like less memory requirements in libpq, less new users with
why is my query so slow before the first row, and almost no concerns. A
small overhead of row description messages perhaps. Maybe the biggest
benefit of a pgsetfetchsize api call would be to raise awareness that of
the fact that pgresults are transfered completely (or partially if there
is animo for me/collegue of mine working on a patch for this).

Besides that, another approach to get data to clients faster could be by
perhaps using lzo, much in the same way that google uses zippy (see e.g.
http://feedblog.org/2008/10/12/google-bigtable-compression-zippy-and-bmdiff/)
to speed up data transfer and delivery. LZO has been mentioned before on
mailing lists for pg_dump compression, but I think that with a
--enable-lzo also libpq could benefit too.
(http://archives.postgresql.org/pgsql-performance/2009-08/msg00053.php)

regards,
Yeb Havinga

Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
> What if the default operation of e.g. php using libpq would be as
> follows: set some default fetchsize (e.g. 1000 rows), then just issue
> getrow. In the php pg handling, a function like getnextrow would wait
> for the first pgresult with 1000 rows. Then if the pgresult is depleted
> or almost depleted, request the next pgresult automatically. I see a lot
> of benefits like less memory requirements in libpq, less new users with
> why is my query so slow before the first row, and almost no concerns.

You are blithely ignoring the reasons why libpq doesn't do this. The
main one being that it's impossible to cope sanely with queries that
fail partway through execution. The described implementation would not
cope tremendously well with nonsequential access to the resultset, either.

regards, tom lane

Tom Lane wrote:
> Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
>
>> What if the default operation of e.g. php using libpq would be as
>> follows: set some default fetchsize (e.g. 1000 rows), then just issue
>> getrow. In the php pg handling, a function like getnextrow would wait
>> for the first pgresult with 1000 rows. Then if the pgresult is depleted
>> or almost depleted, request the next pgresult automatically. I see a lot
>> of benefits like less memory requirements in libpq, less new users with
>> why is my query so slow before the first row, and almost no concerns.
>>
>
> You are blithely ignoring the reasons why libpq doesn't do this. The
> main one being that it's impossible to cope sanely with queries that
> fail partway through execution.
I'm sorry I forgot to add a reference to your post of
http://archives.postgresql.org/pgsql-general/2010-02/msg00956.php which
is the only reference to queries failing partway that I know of. But
blithely is not a good description of me ignoring it. I though about how
queries could fail, but can't think of anything else than e.g. memory
exhaustion, and that is just one of the things that is improved this
way. Maybe a user defined type with an error on certain data values, but
then the same arguing could be: why support UDT? And if a query fails
during execution, does that mean that the rows returned until that point
are wrong?
> The described implementation would not
> cope tremendously well with nonsequential access to the resultset, either.
>
That's why I'm not proposing to replace the current way pgresults are
made complete, but just an extra option to enable developers using the
libpq libary making the choice themselves.

regards,
Yeb Havinga

On Thu, Mar 18, 2010 at 1:21 PM, Yeb Havinga <yebhavinga(at)gmail(dot)com> wrote:
> Tom Lane wrote:
>>
>> Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
>>
>>>
>>> What if the default operation of e.g. php using libpq would be as
>>> follows: set some default fetchsize (e.g. 1000 rows), then just issue
>>> getrow. In the php pg handling, a function like getnextrow would wait for
>>> the first pgresult with 1000 rows. Then if the pgresult is depleted or
>>> almost depleted, request the next pgresult automatically. I see a lot of
>>> benefits like less memory requirements in libpq, less new users with why is
>>> my query so slow before the first row, and almost no concerns.
>>>
>>
>> You are blithely ignoring the reasons why libpq doesn't do this.  The
>> main one being that it's impossible to cope sanely with queries that
>> fail partway through execution.
>
> I'm sorry I forgot to add a reference to your post of
> http://archives.postgresql.org/pgsql-general/2010-02/msg00956.php which is
> the only reference to queries failing partway that I know of. But blithely
> is not a good description of me ignoring it. I though about how queries
> could fail, but can't think of anything else than e.g. memory exhaustion,
> and that is just one of the things that is improved this way. Maybe a user
> defined type with an error on certain data values, but then the same arguing
> could be: why support UDT? And if a query fails during execution, does that
> mean that the rows returned until that point are wrong?
>>
>>  The described implementation would not
>> cope tremendously well with nonsequential access to the resultset, either.
>>
>
> That's why I'm not proposing to replace the current way pgresults are made
> complete, but just an extra option to enable developers using the libpq
> libary making the choice themselves.

This seems pretty reasonable to me, especially considering that JDBC
is apparently already doing it. I suppose there will always be
projects that want to reimplement the backend protocol so that they
can be "pure" some-language, but chipping away at the list of other
reasons why someone might not want to use libpq still seems like a
good idea.

...Robert

Robert Haas wrote:
> This seems pretty reasonable to me, especially considering that JDBC
> is apparently already doing it. I suppose there will always be
> projects that want to reimplement the backend protocol so that they
> can be "pure" some-language, but chipping away at the list of other
> reasons why someone might not want to use libpq still seems like a
> good idea.
>
> ...Robert
>
Hello Robert,

Thank you for your support, but am unsure if it is wise to put time in
it due to the other reactions.

regards,
Yeb Havinga