agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Sebastien FLAESCH <sf@4js.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: libpq: How are result sets fetched behind the scene?
Date: Tue, 10 Sep 2019 11:11:39 +0200
Message-ID: <72a71e9e-001c-f2fa-d1e4-3d65913589ab@4js.com> (raw)
In-Reply-To: <24795.1567971338@sss.pgh.pa.us>
References: <6f2f210b-53e6-f350-4850-83f49b2a1354@4js.com>
	<7e0c3606-1513-1120-555a-6a14b615d939@4js.com>
	<87zhjg8eep.fsf@x230.onfire.org>
	<24795.1567971338@sss.pgh.pa.us>

Thanks you for your answers Tom and Christian!

Our code is based on server cursors to fetch rows by groups with FETCH FORWARD.

This is fine and optimal regarding memory used by the result set.

I think my main message here is that DECLARE / server cursors execution speed
should be optimized, if possible.

We did some comparisons with other DB engines, and they are much faster with
equivalent query execution API calls.

Seb

On 9/8/19 9:35 PM, Tom Lane wrote:
> Christian Barthel <bch@online.de> writes:
>> Sebastien FLAESCH <sf@4js.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
> 
> 

view thread (6+ messages)

Message-ID: <72a71e9e-001c-f2fa-d1e4-3d65913589ab@4js.com>
Permalink:  ../72a71e9e-001c-f2fa-d1e4-3d65913589ab@4js.com/
Also on:    postgresql.org/message-id/72a71e9e-001c-f2fa-d1e4-3d65913589ab@4js.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sf@4js.com, pgsql-sql@lists.postgresql.org
  Subject: Re: libpq: How are result sets fetched behind the scene?
  In-Reply-To: <72a71e9e-001c-f2fa-d1e4-3d65913589ab@4js.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox