To: pgsql-sql@lists.postgresql.org
From: Sebastien FLAESCH <sf@4js.com>
Subject: libpq: How are result sets fetch behind the scene?
Organization: Four Js Development Tools
Message-ID: <6f2f210b-53e6-f350-4850-83f49b2a1354@4js.com>
Date: Thu, 29 Aug 2019 18:32:44 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi all,

We are using libpq in our C program.

I was wondering how the client lib / protocol manage to fetch the result set to the client app memory.

Is there some optimization ?

Are rows fetched in sequence until the requested row?

I mean if I do a SELECT that produces a huge result set, and then call PQgetvalue() with a row_number = 5000, are all 5000 rows fetched from the 
server or is there some smart fast-forward done because I did not ask the 4999 previous rows?

We know about server cursors (declare + fetch forward), and we do already fetch blocks of rows with this.

But server cursors are slow (and certainly use more server resources), compared to direct SELECT execution.

So we are wondering what would be the impact in terms of resources on the client application.

Is there any doc link or blog that would explain best practices with libpq result set programming?

Thanks!
Seb