Обсуждение: 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 https://odbc.postgresql.org/docs/config.html 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
Hi Markus, On 2018/11/06 7:34, Markus Angst wrote: > 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 https://odbc.postgresql.org/docs/config.html 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." 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 > > > 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
On 2018/11/06 13:21, Inoue, Hiroshi wrote: > Hi Markus, > > On 2018/11/06 7:34, Markus Angst wrote: >> 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 https://odbc.postgresql.org/docs/config.html 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." > > Hmm, it seems a documentation bug to me. > > "If not using cursors, this is how many tuples to allocate memory at > first." > > seems correct. Oops, I was wrong. "If not using cursors, this has no meaning." seems right. > > 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 > >> >> >> 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 --- このメールは、AVG によってウイルス チェックされています。 http://www.avg.com
Hi Hiroshi, >>> 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 https://odbc.postgresql.org/docs/config.html 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." >> >> Hmm, it seems a documentation bug to me. >> >> "If not using cursors, this is how many tuples to allocate memory at >> first." >> >> seems correct. > > Oops, I was wrong. > > "If not using cursors, this has no meaning." > > seems right. Thanks for your reply. What is the exact memory limit (and, out of curiosity: why does it exist)? Regards Markus Angst
On 2018/11/07 1:10, Markus Angst wrote: > Hi Hiroshi, > >>>> 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 https://odbc.postgresql.org/docs/config.html 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." >>> >>> Hmm, it seems a documentation bug to me. >>> >>> "If not using cursors, this is how many tuples to allocate memory at >>> first." >>> >>> seems correct. >> >> Oops, I was wrong. >> >> "If not using cursors, this has no meaning." >> >> seems right. > > Thanks for your reply. What is the exact memory limit (and, out of > curiosity: why does it exist)? 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 > > Regards > Markus Angst --- このメールは、AVG によってウイルス チェックされています。 http://www.avg.com
>>>>> 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 https://odbc.postgresql.org/docs/config.html 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." >>>> >>>> Hmm, it seems a documentation bug to me. >>>> >>>> "If not using cursors, this is how many tuples to allocate memory at >>>> first." >>>> >>>> seems correct. >>> >>> Oops, I was wrong. >>> >>> "If not using cursors, this has no meaning." >>> >>> seems right. >> >> Thanks for your reply. What is the exact memory limit (and, out of >> curiosity: why does it exist)? > > 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. 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. > BTW simple queries rather consume large amount of memory. > Could you narrow down the conditions of your queries? 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