Server memory exhausted on ODBC 8.3.0400

Hi,

Last weekend, I update 2 customers servers with the latest Postgresql
8.3.5 vrsion. The databases in each customer have +/- 50GB of data.

I changed some ODBCs versions in clients machines from 7.3.0208 to 8.3.0400.
In this machines, when I run some reports (thats run million of small
queries), the Postgresql server crashes because this processes use all
physical and swap memory in the server.

If I disable both 'Use declare/fetch' and 'Updatable Cursor', the error
does not occurs. I don't need the 'Updatable Cursors', but the 'Use
Declare Fetch' is crucial to me.
In ODBC 7.3.0208, with this option enabled, the error doesn't occurs.

I think that use of memory grows in sort operations (If I reduce the
work_mem, the memory grows not so fast). But the queries are too fast
(average 0.005 ms). I think that after the query is executed, the work
memory should be released.

Is SAVEPOINT/RELEASE statements generated by ODBC 8.3.0400 (and not
generated by 7.3) the cause of the problem ???


Best regards,


Alexandre


Server 1:
Debian 4.0 etch
Postgresql 8.3.5 compiled from sources.
16 Gb RAM
shared buffers = 2GB
work_mem = 64MB

Server 2:
CentOS 5
Postgresql 8.3.5 compiled from sources.
8 Gb RAM
shared buffers = 1GB
work_mem = 32MB



--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Server memory exhausted on ODBC 8.3.0400

alepaes - aldeia digital wrote:
> Hi,
>
> Last weekend, I update 2 customers servers with the latest Postgresql
> 8.3.5 vrsion. The databases in each customer have +/- 50GB of data.
>
> I changed some ODBCs versions in clients machines from 7.3.0208 to
> 8.3.0400.
> In this machines, when I run some reports (thats run million of small
> queries), the Postgresql server crashes because this processes use all
> physical and swap memory in the server.
>
> If I disable both 'Use declare/fetch' and 'Updatable Cursor', the error
> does not occurs. I don't need the 'Updatable Cursors', but the 'Use
> Declare Fetch' is crucial to me.
> In ODBC 7.3.0208, with this option enabled, the error doesn't occurs.

Does your code close the statements used in the applications?

regards,
Hiroshi Inoue

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Server memory exhausted on ODBC 8.3.0400

Hi Hiroshi,

Hiroshi Inoue wrote:
> alepaes - aldeia digital wrote:
>> Hi,
>>
>> Last weekend, I update 2 customers servers with the latest Postgresql
>> 8.3.5 vrsion. The databases in each customer have +/- 50GB of data.
>>
>> I changed some ODBCs versions in clients machines from 7.3.0208 to
>> 8.3.0400.
>> In this machines, when I run some reports (thats run million of small
>> queries), the Postgresql server crashes because this processes use all
>> physical and swap memory in the server.
>>
>> If I disable both 'Use declare/fetch' and 'Updatable Cursor', the
>> error does not occurs. I don't need the 'Updatable Cursors', but the
>> 'Use Declare Fetch' is crucial to me.
>> In ODBC 7.3.0208, with this option enabled, the error doesn't occurs.
>
> Does your code close the statements used in the applications?

Basically, the program starts with a BEGIN and make a lot of simple
SELECT queries and some insert and update queries. After this we have a
COMMIT. Very simple.
Like I sad, If I disable "Use declare" and "Updatable", the error does
not occurs. If I enable one of this, the error occurs. I test in some
other heavy applications and the error persists. If I run this in an
older ODBC driver, everything if fine.

Regards,

Alexandre



--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Server memory exhausted on ODBC 8.3.0400

alepaes - aldeia digital wrote:
> Hi Hiroshi,
>
> Hiroshi Inoue wrote:
>> alepaes - aldeia digital wrote:
>>> Hi,
>>>
>>> Last weekend, I update 2 customers servers with the latest Postgresql
>>> 8.3.5 vrsion. The databases in each customer have +/- 50GB of data.
>>>
>>> I changed some ODBCs versions in clients machines from 7.3.0208 to
>>> 8.3.0400.
>>> In this machines, when I run some reports (thats run million of small
>>> queries), the Postgresql server crashes because this processes use
>>> all physical and swap memory in the server.
>>>
>>> If I disable both 'Use declare/fetch' and 'Updatable Cursor', the
>>> error does not occurs. I don't need the 'Updatable Cursors', but the
>>> 'Use Declare Fetch' is crucial to me.
>>> In ODBC 7.3.0208, with this option enabled, the error doesn't occurs.
>>
>> Does your code close the statements used in the applications?
>
> Basically, the program starts with a BEGIN and make a lot of simple
> SELECT queries and some insert and update queries. After this we have a
> COMMIT. Very simple.

What kind of tool are you using?
Do you close cursors before "COMMIT" using SQLCloseCursor() or
the coressponding command in your tool?

regards,
Hiroshi Inoue

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Server memory exhausted on ODBC 8.3.0400

Hiroshi Inoue wrote:
> alepaes - aldeia digital wrote:
>> Hi Hiroshi,
>>
>> Hiroshi Inoue wrote:
>>> alepaes - aldeia digital wrote:
>>>> Hi,
>>>>
>>>> Last weekend, I update 2 customers servers with the latest
>>>> Postgresql 8.3.5 vrsion. The databases in each customer have +/-
>>>> 50GB of data.
>>>>
>>>> I changed some ODBCs versions in clients machines from 7.3.0208 to
>>>> 8.3.0400.
>>>> In this machines, when I run some reports (thats run million of
>>>> small queries), the Postgresql server crashes because this processes
>>>> use all physical and swap memory in the server.
>>>>
>>>> If I disable both 'Use declare/fetch' and 'Updatable Cursor', the
>>>> error does not occurs. I don't need the 'Updatable Cursors', but the
>>>> 'Use Declare Fetch' is crucial to me.
>>>> In ODBC 7.3.0208, with this option enabled, the error doesn't occurs.
>>>
>>> Does your code close the statements used in the applications?
>>
>> Basically, the program starts with a BEGIN and make a lot of simple
>> SELECT queries and some insert and update queries. After this we have
>> a COMMIT. Very simple.
>
> What kind of tool are you using?
> Do you close cursors before "COMMIT" using SQLCloseCursor() or
> the coressponding command in your tool?

I'm 'only' the DBA... ;)
The software was made by a CASE Tool called Genexus, thats generate the
software in Visual Basic 6 and use ODBC for connectivity. This tool
doesn't make any use of cursors, functions, triggers or stored
procedures, only simple SELECTs and DML statements. The cursors are
generated by 'Use Declare/Fetch' in ODBC. This improves performance a
lot. Analyzing the log, The only differences that I notice between the
old and the new driver is the use of SAVEPOINT/RELEASE and some changes
in the way of creation of DECLARE/FETCH.

I will try to reproduce this by making a simple application that loops
some SELECT, INSERT and UPDATE statement to see what happens.

Best regards,

Alexandre

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc