DevHeads.net

Out of memory while reading tuples

Hi,

Postgres 10.4 64bit on Windows
psqlODBC 10.03.0000
- Use Declare/Fetch is off
- Cache size is 10'000'000

Issuing a simple select statement with Microsoft Access gives me the following
ODBC error:

"Out of memory while reading tuples.; memory allocation error??? (#4)"

The statement returns 822701 rows (via JDBC), average row size is 100 bytes.

On <a href="https://odbc.postgresql.org/docs/config.html" title="https://odbc.postgresql.org/docs/config.html">https://odbc.postgresql.org/docs/config.html</a> it says:
"Cache Size: When using cursors, this is the row size of the tuple cache. If not
using cursors, this is how many tuples to allocate memory for at any given time.
The default is 100 rows for either case."

Setting the field to the aforementioned 10'000'000 does not help. Is this value
ignored? Is there a maximum value for Cache size? How is the tuple size calculated?

Setting Use Declare/Fetch is not an option. Access can have severe problems with
this setting; an experience I don't want to repeat.

My plan B would be to select the data in several pieces, which would be much
more complicated in this particular case.

What else can I do?

Thanks and best regards
Markus Angst

Comments

Re: Out of memory while reading tuples

By Inoue, Hiroshi at 11/06/2018 - 00:21

Hi Markus,

On 2018/11/06 7:34, Markus Angst wrote:
Hmm, it seems a documentation bug to me.

  "If not using cursors, this is how many tuples to allocate memory at
first."

seems correct.
So "Cache size" doesn't have much meaning if *Use Declare/Fetch* is off.
I think there's no way to avoid *Out of memory while reading tuples* errors
in *Use Declare/Fetch* off mode unfortunately.

regards,
Hiroshi inoue

Re: Out of memory while reading tuples

By Inoue, Hiroshi at 11/06/2018 - 08:17

On 2018/11/06 13:21, Inoue, Hiroshi wrote:
Oops, I was wrong.

  "If not using cursors, this has no meaning."

seems right.

Re: Out of memory while reading tuples

By Markus Angst at 11/06/2018 - 12:10

Hi Hiroshi,

Thanks for your reply. What is the exact memory limit (and, out of curiosity:
why does it exist)?

Regards
Markus Angst

Re: Out of memory while reading tuples

By Inoue, Hiroshi at 11/06/2018 - 22:17

On 2018/11/07 1:10, Markus Angst wrote:
Psqlodbc itself doesn't have such a limitation. *Out of memory* means
that psqlodbc requested
a memory area using m(re)alloc function but the system replied that it's
a tall order.

BTW simple queries rather consume large amount of memory.
Could you narrow down the conditions of your queries?

regards,
Hiroshi Inoue

Re: Out of memory while reading tuples

By Markus Angst at 11/07/2018 - 13:11

Well, I don't know enough about the inner workings of windows, but the RAM usage
goes up from about 200 MB to about 400 MB before the message appears (with only
the Access client running on this machine that has 4 GB of RAM). That doesn't
sound too tight to me.

Yes, I could, but it would be somewhat artificial, because it is a data export :-)

Anyway, I am about to switch this particular export to ADO/OLEDB to circumvent
the problem.

Thank you
Markus