Обсуждение: Memory leaks in PostgreSQL ODBC driver

It seems that when you perform a large query against the postgresql ODBC driver
it sucks it all in to memory. If the query returns more data than you have
memory, it fails. To change this behavior, you set UseDeclareFetch to yes.

It seems that in QR_read_tuple, a buffer is allocated but never freed. Has
anyone ever seen this before?

I traced this in the unixODBC Postgre7.1 driver by replacing free/malloc with
debug versions, and it looks like the PostgreSQL version has the same issue.

Could this buffer be freed else where?

mlw wrote:
>
> It seems that when you perform a large query against the postgresql ODBC driver
> it sucks it all in to memory. If the query returns more data than you have
> memory, it fails. To change this behavior, you set UseDeclareFetch to yes.
>
> It seems that in QR_read_tuple, a buffer is allocated but never freed. Has
> anyone ever seen this before?
>
> I traced this in the unixODBC Postgre7.1 driver by replacing free/malloc with
> debug versions, and it looks like the PostgreSQL version has the same issue.
>
> Could this buffer be freed else where?
>

The Windows odbc driver does this as well, which is not surprising, given that
it is probably the same code.

Question, could this be because the ODBC cursor expects to be able to scroll
backwards? FORWARD_ONLY is the default, if I'm not mistaken.

In qresult.c:QR_next_tuple(), cache_size keeps growing. Shouldn't the driver
test for FORWARD_ONLY, in which case it should replace the previous chunk of
tuples, rather than add them?

Keeping all the previous tuples in a declare/fetch query, with a FORWARD_ONLY
cursor fundamentally cripples the ODBC driver for applications which intend to
process a great deal of data. I know I can't use the PostgreSQL ODBC driver for
my application.

mlw wrote:
>
> mlw wrote:
> >
> > It seems that when you perform a large query against the postgresql ODBC driver
> > it sucks it all in to memory. If the query returns more data than you have
> > memory, it fails. To change this behavior, you set UseDeclareFetch to yes.
> >
> > It seems that in QR_read_tuple, a buffer is allocated but never freed. Has
> > anyone ever seen this before?
> >
> > I traced this in the unixODBC Postgre7.1 driver by replacing free/malloc with
> > debug versions, and it looks like the PostgreSQL version has the same issue.
> >
> > Could this buffer be freed else where?
> >
>
> The Windows odbc driver does this as well, which is not surprising, given that
> it is probably the same code.

Could you try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/ ?

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

mlw wrote:
>
> mlw wrote:
> >
> > It seems that when you perform a large query against the
> > postgresql ODBC driver it sucks it all in to memory. If
> > the query returns more data than you have memory, it fails.
> > To change this behavior, you set UseDeclareFetch to yes.

Your report is all about UseDeclareFetch mode ?
If so, I would examine it.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

Hiroshi Inoue wrote:
>
> mlw wrote:
> >
> > mlw wrote:
> > >
> > > It seems that when you perform a large query against the
> > > postgresql ODBC driver it sucks it all in to memory. If
> > > the query returns more data than you have memory, it fails.
> > > To change this behavior, you set UseDeclareFetch to yes.
>
> Your report is all about UseDeclareFetch mode ?
> If so, I would examine it.

After spending some time and getting real friendly with the ODBC driver, I have
come to realize what I was seeing was not a memory leak, exactly, but a
misunderstanding about how it should work.

My expectation is that if I use UseDeclareFetch mode with a FORWRD_ONLY cursor,
previous results are lost as new results are obtained. I believe the PostgreSQL
ODBC driver is in error, because even with UseDeclareFetch, and a FORWARD_ONLY
cursor, the previous results are not freed, thus preventing the ODBC driver
from querying data sources which can provide more data than can fit in
available RAM.

Is this not correct?

mlw wrote:
>
> Hiroshi Inoue wrote:
> >
> > mlw wrote:
> > >
> > > mlw wrote:
> > > >
> > > > It seems that when you perform a large query against the
> > > > postgresql ODBC driver it sucks it all in to memory. If
> > > > the query returns more data than you have memory, it fails.
> > > > To change this behavior, you set UseDeclareFetch to yes.
> >
> > Your report is all about UseDeclareFetch mode ?
> > If so, I would examine it.
>
> After spending some time and getting real friendly with the ODBC
> driver, I have
> come to realize what I was seeing was not a memory leak, exactly,
> but a misunderstanding about how it should work.
>
> My expectation is that if I use UseDeclareFetch mode with a
> FORWRD_ONLY cursor, previous results are lost as new results
> are obtained. I believe the PostgreSQL ODBC driver is in error,
> because even with UseDeclareFetch, and a FORWARD_ONLY cursor,
> the previous results are not freed, thus preventing the ODBC
> driver from querying data sources which can provide more data
> than can fit in available RAM.
>
> Is this not correct?

Maybe you are right.
The snapshot dll at my page would fix one of the causes.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/