Skip site navigation (1) Skip section navigation (2)

Re: C# w/ ODBC, 2.1 million list select gives empty DataSet

From: Laurent Chouinard <laurent(dot)chouinard(at)sem(dot)ca>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: C# w/ ODBC, 2.1 million list select gives empty DataSet
Date: 2010-01-13 20:34:07
Message-ID: 4B4E2E3F.9090401@sem.ca (view raw or flat)
Thread:
Lists: pgsql-odbc
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

In response to

pgsql-odbc by date

Next:From: the6campbellsDate: 2010-01-17 16:03:12
Subject: sum(decimal) not returning dp when query run via ODBC on Linux
Previous:From: Jonah H. HarrisDate: 2010-01-13 19:48:21
Subject: Re: Hi!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group