Re: Problems with ODBC and ASP .NET 2.0

We use both, our .net (aprx 10% of our app uses npgsql).
Our asp uses the 7.4 odbc driver.

I guess I am a little unsure what you mean here (not that you were unclear).
Do I just turn cursor fetch in the odbc connect string?

I may have answered some of the memory bloat questions in writing conversion
utilities.
I can see for example the program I wrote to go from SQLASCII to UNICODE
(uses npgsql) reads the whole table into memory (crashes on the really large
tables).
It definitely just sucks up memory while reading (uses a reader, so not sure
if it is in .net or the npgsql).
I do close all record sets and connections as well as set readers to null
and close connection in .net.
I did not play around with the actual setting like how to open the
connection with fetch on or something.

Joel Fradkin

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305

jfradkin(at)wazagua(dot)com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.

-----Original Message-----
From: Marko Ristola [mailto:Marko(dot)Ristola(at)kolumbus(dot)fi]
Sent: Wednesday, July 13, 2005 6:22 AM
To: Joel Fradkin
Cc: 'Greg Campbell'; 'C Funky'; pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Problems with ODBC and ASP .NET 2.0

About ASP .NET, npgsql and C#

So, because you use now npgsql, the case is a bit different:
I checked the npgsql documentation.

You can overcome the memory hogging problem at least
with the refcursor style (see npgsql docs):
1. create a function for cursor creation.
2. read the results one row at a time.

psqlodbc is much faster with the "fetch cursor" implementation,
because it fetches for example 1000 rows (selectable) from the
Linux PostgreSQL server with one internal fetch.

npgsql: ask one row from PostgreSQL server. process it.
Ask the next row from PostgreSQL server.

psqlodbc: ask 1000 rows from PostgreSQL server. process them.
ask 1000 more. ...

So both work the same way, but the network latency affects with
npgsql once per row, and with psqlodbc once per 1000 rows.
psqlodbc method is a lot faster, because of that.
With my home measurement, with C coding,
the slowdown because of network latency
with psqlodbc in 10 Mbit WLAN is about 10%.
Please measure it yourself, when facts needed.

"fetch cursor" under psqlodbc is invisible to the ODBC client.
Just configure, and it will be used.

(psqlodbc cursor using method is not optimal for
"read everything", as I have told in the previous
message about PostgreSQL backend planner
assumptions based on cursor usage).

Better to ask from the npgsql people about the crashing.
They might have lots of knoledge about the area.

I have never experimented with npgsql. Memory hogging
problematics affects it also, because it runs under a 32 bit machine.
Under a virtual machine, the memory problems are even
more acute than in C or C++. There are good enough
memory saving algorithms, at least in algorithm books.

Marko Ristola

Joel Fradkin wrote:

>TCP/IP network port space is 16 bits, so at most 65536 ports
>can be open at one time. This is sometimes a problem, because
>one port is locked for one connection some time after closing the
connection
>(maybe it takes half a minute before it can be reused). So to overcome
>this problem,
>retrying connection after 10 seconds is fine.
>
>PostgreSQL backend has a limit on the number of allowed connections.
>Of course, if you like, you might try to set it into 65536. Bigger number
>might decrease performance a bit. To overcome the rare case when the
>connection barrier has been reached, reconnecting by the client side
>is fine.
>
>JF reply:
>I could up the number (is 600 now) not sure what error gets reported, but
>when it was 300 I think I saw the error and changed to 600. I don't think
>this is my issue.
>
>So these problems don't cause crashes even though the npgsql is
>completely unknown for me.
>
>Memory allocation problems do cause crashes. SQLAllocHandle() does
>allocate memory,
>but it don't try to create a connection. On Linux, this would have just
>crashed.
>On Windows, memory allocation problem has been reported to the user???
>JF reply:
>Any idea the error ? I did get twice something about SQLAllocHandle() which
>is new after I got the latest mdac. I still get "catastrophic error" from
>the odbc driver error text. I was up for 7 days no crash then I crashed
>twice once Sat and once today, so I am truly puzzled. After the crashes I
>rebooted both IIS servers (only one is crashing with error message) and the
>Linux Postgres server.
>
>
>Even though you have 4GB memory, the 32-bit address space even on
>Windows and especially on IIS, might be very limited. The available
>memory for each IIS client might well be under 1GB.
>Please try to find out on IIS manuals or from Microsoft.
>JF reply:
>Win 2k standard gives 2 gigs max (I may go to windows 2003 which will use
>all 4 gig), but when the IIS server locks up in odbc land it is typically
>less then a gig 550 meg today.
>
>For example on Linux: the last 1G is reserved for the Linux kernel.
>Ealier there was maybe only 1G for the program and it's data. There was
>1G for the
>program stack. For memory mapped files there was also some space reserved.
>
>So with 64 bit Linux or Windows, the memory space is luxorious. Even
>though you
>have only 1GB memory, if you have enough swap space, you don't have to
worry
>about memory allocation crashes. You can increase the Linux kernel
>address limits, when needed.
>
>Of course, the memory might become problematic on various reasons:
>
>- you need to allocate 100MB space, even though the largest continuous
>memory segment is
> only 90MB and there is 2G memory free. Try to allocate the memory in
>smaller pieces.
>
>JF reply:
>Not sure what you mean, how do I alloc memory differently?
>
>- you need to read 1000 million rows from the database. All of it will
>be stored into the 32bit addressed memory before use. The program
>crashes. Don't do it. If you need to do it, use cursor or some other
>similar method. (cursors are not efficient here, because PostgreSQL
>assumes, that you read only about 1% of the rows on the table).
>
>JF reply:Maybe we can talk off list to get specific, some of my sql will
>return large data sets (reporting etc). I would think if it was a memory
>issue I would see the IIS server at peak memory usage (2 gig in this case).
>I did have leaks prior to convering to win2k ;was NT4; and I had to fix
>those to get on win2k.
>
>- you'r program doesn't have huge memory requirements, but somewhere is
>a memory leakage. It leaks memory 10MB in a day. So if the memory limit
>comes at 500MB, the crash comes after 50 days. One fix for this is to
>restart the service once a day, like many Linux services are restarted
>each morning.
>- even memory fragmentation in C might be a problem after a year of good
>service.
>
>- Software stability problems -> try to learn about testing (for example
>"Practical Testing")
> book.
>
>I wish success for you, Joel, for overcoming the problems.
>Marko Ristola
>
>JF reply:
>I appreciate all your ideas and help it means a lot, Thanks for taking the
>time to help me. I have done testing using the windows stress tool etc, but
>nothing hurts me like real production, so I must be kinda lame at setting
up
>good test scenerios. I have tried adding, deleting,and reporting etc in the
>stress tool with several computers hitting the test environment.
>
>
>Joel Fradkin wrote:
>
>
>
>>I have heavy use of odbc because I am still asp 70% or so.
>>I use npgsql for my .net stuff too.
>>Pervasive is supposed to be updating the odbc driver in thee near future
(I
>>hope it works as my app is crashing often and my clients are going to drop
>>us if I can not get this resolved).
>>
>>I am using the 7.4 drivers also because my data has Unicode (French) chars
>>and is stored in a SQLASCII data base. I have the conversion program
>>
>>
>written
>
>
>>and tested to get on a Unicode database and then I can try the 8.0 odbc
>>drivers.
>>
>>Not sure where you turned on pooling and set the time out, I would be
>>interested in that. I just got a Driver's SQLAllocHandle on SQL_HANDLE_DBC
>>failed error this morning.
>>
>>Joel Fradkin
>>
>>Wazagua, Inc.
>>2520 Trailmate Dr
>>Sarasota, Florida 34243
>>Tel. 941-753-7111 ext 305
>>
>>jfradkin(at)wazagua(dot)com
>>www.wazagua.com
>>Powered by Wazagua
>>Providing you with the latest Web-based technology & advanced tools.
>>C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
>>This email message is for the use of the intended recipient(s) and may
>>contain confidential and privileged information. Any unauthorized review,
>>use, disclosure or distribution is prohibited. If you are not the
intended
>>recipient, please contact the sender by reply email and delete and destroy
>>all copies of the original message, including attachments.
>>
>>
>>
>>
>>-----Original Message-----
>>From: pgsql-odbc-owner(at)postgresql(dot)org
>>[mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Greg Campbell
>>Sent: Friday, July 08, 2005 8:31 PM
>>To: C Funky
>>Cc: pgsql-odbc(at)postgresql(dot)org
>>Subject: Re: [ODBC] Problems with ODBC and ASP .NET 2.0
>>
>>Not too sure I can help you.
>>After looking into what was available I decided to use the Npgsql
>>instead of ODBC. It is very much more ADO.NET centric.
>>I have a app running for a couple of months continuous with moderate
>>load with nary a glitch.
>>
>>I trust pooling to work, so all my "connect - do something - drop
>>connection" are very short, and used a lot, natch.
>>
>>
>>
>>
>>
>>
>>
>>C Funky wrote:
>>
>>
>>
>>
>>>Hi guys
>>>
>>>I've got a ASP .NET application running on Windows Server 2003
>>>accessing Postgresql (version 8.0) through ODBC on the 7.4 drivers and
>>>I've run into a bit of a problem. Under very light loads (a couple
>>>connections per hour) the application works fine. However, as the load
>>>increases, I start getting ODBC exceptions occasionally when the
>>>application tries to open a new connection. The exception contains no
>>>information other than something about being unable to determine the
>>>driver version. When this error first started popping up I'd have to
>>>restart the postmaster service for anything to work. Then, after
>>>turning on ODBC connection pooling and setting a timeout of 10 s, I
>>>could just wait a couple seconds, retry creating the connection and
>>>everything would work fine. Does anyone out there have any idea why
>>>this might be happening? I'm pretty new to Postgresql, so please
>>>forgive me if this is something super obvious that I've just missed.
>>>
>>>Thanks,
>>>Chris
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: don't forget to increase your free space map settings
>>>
>>>
>>>
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: explain analyze is your friend
>>
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>