Thread: Out of memory troubles with version 8.01.01.02

Out of memory troubles with version 8.01.01.02

From
Filip Wuytack
Date:
Hi All,

I'm trying to fetch a table (simple select * from table) which is 102
columns and some 3.5 million rows. When running this I always end up
having a "out of memory". (when I put a where statement or a limit in the
SQL, it runs fine). I've tried tweaking the settings for the odbc setting
in windows (is windows xp) and upgraded to version  8.01.01.02 (even tried
to 103 snapshot), but the problem remains. I've attached both log files
created by the odbc driver. I hope someone can give me some hints on way
this is happening. I'm accessing the odbc dsn from within SAS 9.1.

Many thanks,

Filip


Attachment

Re: Out of memory troubles with version 8.01.01.02

From
Ludek Finstrle
Date:
> I'm trying to fetch a table (simple select * from table) which is 102
> columns and some 3.5 million rows. When running this I always end up
> having a "out of memory". (when I put a where statement or a limit in the
> SQL, it runs fine). I've tried tweaking the settings for the odbc setting
> in windows (is windows xp) and upgraded to version  8.01.01.02 (even tried
> to 103 snapshot), but the problem remains. I've attached both log files
> created by the odbc driver. I hope someone can give me some hints on way
> this is happening. I'm accessing the odbc dsn from within SAS 9.1.

Hello,

  I know about this limitation. There is problem ODBC keep all rows
in memory (+ result from libpq = 2 x result). Please try check
Use Declare/Fetch option. There is no twice result in memory in psqlODBC.
If it doesn't help you can try older releases which mayde do not do this.
Please try 8.00.X or even 7.03.209 (if you find it somewhere).
I know we're suggesting 8.01.X but few people work with so big
datasets.
You can buy more memory too (only as quick hack if you need it fast).

We have a lot of bug reports (and so few developers) so I don't know
when we focus on this problem.

Regards,

Luf

Re: Out of memory troubles with version 8.01.01.02

From
tomas@nocrew.org (Tomas Skäre)
Date:
Ludek Finstrle <luf@pzkagis.cz> writes:

>   I know about this limitation. There is problem ODBC keep all rows
> in memory (+ result from libpq = 2 x result). Please try check
> Use Declare/Fetch option. There is no twice result in memory in psqlODBC.
> If it doesn't help you can try older releases which mayde do not do this.
> Please try 8.00.X or even 7.03.209 (if you find it somewhere).
> I know we're suggesting 8.01.X but few people work with so big
> datasets.

Does it keep everything in memory when using cursors too?


Tomas

Re: Out of memory troubles with version 8.01.01.02

From
Ludek Finstrle
Date:
> >   I know about this limitation. There is problem ODBC keep all rows
> > in memory (+ result from libpq = 2 x result). Please try check
> > Use Declare/Fetch option. There is no twice result in memory in psqlODBC.
> > If it doesn't help you can try older releases which mayde do not do this.
> > Please try 8.00.X or even 7.03.209 (if you find it somewhere).
> > I know we're suggesting 8.01.X but few people work with so big
> > datasets.
>
> Does it keep everything in memory when using cursors too?

New releases (8.01.X) keep everything in memory. There is no option
to change it. I have an idea how to do it but time ...

Older releases (8.00.X or 7.X - not based on libpq) maybe do not keep
everything in memory.

Regards,

Luf

Re: Out of memory troubles with version 8.01.01.02

From
Filip Wuytack
Date:
Hi all,

Thank you all  for your prompt replies.

I tried about all the versions I could find (ranging from 7.3.200 to
till 8.1.1.03) and they all gave the same trouble.
Then I tried the latest snapshot, 8.1.1.04,and youhouuuuu, it worked!!!
Me a very happy man.
I'm not sure what you did we these dlls, but I just pulled in a +/- 25
milj row table in sas through the odbc (is against a postgresql 8.0.3
amd64 on freebsd 6.0) and it worked perfectly. Memory consumption never
went over 45M in windows. The table takes up 8.6GB of harddisk space in
windows.

Just for future references (in case someone ever has the same error), I
used the following settings:

PostgreSQL ANSI
with the following options:
- disable genetic optimizer
- KSQO
- Recognize Uniq Index
- Use Declare/Fetch
- Inknown sizes: max
- Bools as char
- maxvarchar: 254
- cache: 100
- max long varchar: 8190
- systableprefixes: dd_;
- show system tables
- LF <-> CR/LF
- row versioning
- int8 as default
- protocol >= 6.4
- OID options: show column & fake index

Thanks again,

Filip


Tomas Skäre wrote:
> Ludek Finstrle <luf@pzkagis.cz> writes:
>
>
>>   I know about this limitation. There is problem ODBC keep all rows
>> in memory (+ result from libpq = 2 x result). Please try check
>> Use Declare/Fetch option. There is no twice result in memory in psqlODBC.
>> If it doesn't help you can try older releases which mayde do not do this.
>> Please try 8.00.X or even 7.03.209 (if you find it somewhere).
>> I know we're suggesting 8.01.X but few people work with so big
>> datasets.
>>
>
> Does it keep everything in memory when using cursors too?
>
>
> Tomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>



Re: Out of memory troubles with version 8.01.01.02

From
Ludek Finstrle
Date:
Hello,

  I write this only for clarify.

> Then I tried the latest snapshot, 8.1.1.04,and youhouuuuu, it worked!!!
> Me a very happy man.
> I'm not sure what you did we these dlls, but I just pulled in a +/- 25

Agh, you don't have problem that the result doesn't fit into memory.
Your problem was reported by Dave some days ago.
Driver didn't correctly recognize when it end fetching rows.
Driver fetch same rows forever (which after some time have to lead to
memory problem). This problem was reported as SQLTables doesn't end
or something similar.

> - Use Declare/Fetch

This is the option which lead to the problem with older releases.

Regards,

Luf