Thread: About FetchBufferSize and row caching for postgres odbc

Hi all,

I got a couple of questions about the use of FetchBufferSize (one of
the settings of odbc.ini) and row caching in pgodbc.

Depending on the driver used with ODBC, sometimes FetchBufferSize
means the maximum number of rows, or a maximum size of data that can
be kept on cache on client side. Which definition of FetchBufferSize
does pgodbc use? I assume the max number of rows... But I couldn't
figure out which one it is even by looking at the pgodbc documentation
like the one on pgfoundry

Also, I noticed by googling here and there that some people recommend
a value of FetchBufferSize of sometimes several thousands, like 64000,
or even up to 100. I imagine that a too high value here could cause
some OOM with large rows on client if FetchBufferSize means the max
number of rows. This is a very vague question, but: what is the most
common usage for this parameter, high or low value? Is it better to
give preference to the db-level caching and fetch rows with some extra
fetches?

Finally, I got an extra question about row caching. Is row caching
done each time a fetch is done (SQLFetch, SQLExtendedFetch and
SQLFetchScroll)? Or is it necessary to use some special settings for a
statement handle like for example a dynamic cursor that can be used
with FetchScroll?
Perhaps using one option or the other presents more risks in terms of
high-memory usage?

Is there an area in the code where I should look at in priority to get
an idea of how row caching is done? With a quick glance, qresults.c
with QResultClass?

Regards,
--
Michael

On 14.08.2013 08:26, Michael Paquier wrote:
> I got a couple of questions about the use of FetchBufferSize (one of
> the settings of odbc.ini) and row caching in pgodbc.
>
> Depending on the driver used with ODBC, sometimes FetchBufferSize
> means the maximum number of rows, or a maximum size of data that can
> be kept on cache on client side. Which definition of FetchBufferSize
> does pgodbc use? I assume the max number of rows... But I couldn't
> figure out which one it is even by looking at the pgodbc documentation
> like the one on pgfoundry

psqlodbc doesn't have a parameter called "FetchBufferSize". Setting that
in odbc.ini will therefore have no effect. There is a parameter called
"Fetch", which sets the number of rows to fetch.

- Heikki

On Fri, Aug 16, 2013 at 2:11 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 14.08.2013 08:26, Michael Paquier wrote:
>>
>> I got a couple of questions about the use of FetchBufferSize (one of
>> the settings of odbc.ini) and row caching in pgodbc.
>>
>> Depending on the driver used with ODBC, sometimes FetchBufferSize
>> means the maximum number of rows, or a maximum size of data that can
>> be kept on cache on client side. Which definition of FetchBufferSize
>> does pgodbc use? I assume the max number of rows... But I couldn't
>> figure out which one it is even by looking at the pgodbc documentation
>> like the one on pgfoundry
>
>
> psqlodbc doesn't have a parameter called "FetchBufferSize". Setting that in
> odbc.ini will therefore have no effect. There is a parameter called "Fetch",
> which sets the number of rows to fetch.
Oh I see. Thanks!
--
Michael