On 2010-01-12 23:41, Craig Ringer wrote:
On 12/01/2010 7:34 AM, Laurent Chouinard wrote:
With the new drivers, it still climbs to 700MB or so (makes sense, 2
million rows is quite a bit), and then after the "0 rows" returned,
memory is cleared immediately. Makes sense as well, OBDC doesn't carry
data anymore, dataset is almost null, garbage collector is having a
field day.
I don't use ODBC much personally and don't work on the driver - but it sounds to me a lot like the new driver may be running out of memory too, but is eating the failure (or reporting it via some side-channel) rather than throwing. If it is, that'd be pretty bad behavior in my personal opinion, but then I don't know ODBC well. Someone who does may be able to shed some more light.
My suggestion, though: Even if you resolve this now, as things continue to grow you're still going to have OOM issues. Perhaps it'd be a good idea to use a cursor for this?
You're right. For some reason, it completely escaped me that the new driver could be failing just as the previous one, but in a less acceptable way.
Indeed, the use of a cursor seems logical when dealing with very large quantities of data, especially considering that 2.1 million rows is after only a year of use. My final solution must be future proof to some extent. I tried to have my code be as database-compatible as possible so that I can swap from Postgres to Microsoft easily depending on customer requirements. Using a cursor means I have a PG specific way, but then again, I could also find out how to do that in MS and call that a day. A long one, however!
Thanks all for your inputs.
Also, thanks Hiroshi for your suggestion for the other driver, but I will not venture into non-official releases at the moment because my product is already in use by customers. I can't rely on experimental or beta versions of code.
Regards,
Laurent Chouinard