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