Re: Problems with ODBC and ASP .NET 2.0

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

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

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

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.

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???

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.

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.

- 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).

- 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

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
>
>

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
>
>

Here are my thoughts about your crash problems.
Please check the receipt later, that I wrote as a
plan to overcome the memory hogging problem.

Facts:
You'r program under IIS uses 550 meg memory.

Normal algorithms:
Simple algorithms do the following:
A large memory buffer is 500 meg. It contains the whole
PostgreSQL query result, but a few rows are still to be read.
So the ODBC driver tries to allocate a memory buffer with 1000
meg memory, and copy it's contents from the 500 meg partition
into the first half of the 1000 meg partition.

Psqlodbc does the following thing, if you don't use the
cursor fetching technique, that I have suggested:
First the whole query result is read into a 500 meg buffer.
Then another 500 meg buffer is allocated for conversion
reasons, because the data must be converted from
the retrieved PosgreSQL server data formats into the
required ODBC formats.

So, you can check, that the memory allocation is not memory hog,
with a simple "select * from test" with 10 varchar(100) columns
and ten million rows. It is easy to construct such data.
with psql and COPY format, the table filling is fast.
Or then use INSERTs. Don't use indexes or primary key,
because it just slows testing.

If the ODBC driver is not memory hog, the CGI will use
about 10 meg memory during the whole data read.

If the ODBC driver is memory hog, memory usage will grow continuously
during the whole process runtime.

So, if the ODBC driver is memory hog, configure it not to be
memory hog.

If the ODBC driver is not memory hog, the problem is in your
program.

1. Don't try to read the number of rows at the start of the
SQL query FETCH. That forces the ODBC driver to read all the rows
into memory and crash.

2. Do close the SQL statements after their usage. If you create new
queries, but you don't close the old ones, ODBC driver
might not release the memory resources, and might starve with memroy.

I don't know, how to watch the number of open statements.

If you switch into a commercial ODBC driver, check that it
supports well large enough queries with a small memory
footprint.

Personally I think, that it is okay to switch into a commercial
ODBC driver, if it helps, because for example, I myself have no
responsibility with helping you: this is not my work.

I am in a summer holiday now. As I wrote, I try to help and give
good advices. So my motivation is just to help.

Here is a receipt for you to solve this problem:
1. make sure, that the ODBC driver is not configured to be
memory hog.
2. make your program does not to read the whole query result into the
memory. Just filter the data, as it would be done with Java filtering.
Do anything else, but don't read the whole result into memory in
your program. That will fail, of course!

3. check with a memory hog application feature, that it's memory
footprint in Windows will not go over 100 meg. Otherwise you have a
problem. 500 meg is a very crash risky point!

Additional thoughts:
- Free the statement resources after their use.
The pooling might even lower the available memory per process.
In Windows 2k, it might have 1G available for data and 1 gig available
for the stack. Where will the libraries located? I mean, that it is not
obvious, that you can allocate a 500 meg or 1 gig memory area.
- Of course, with 64bit Windows, memory allocation problems
are history in practice, if you have enough swap space.
- It seems to be an option to "plug in" another ODBC driver
and use it. If it does fix the memory hogging problems, the hogginess
problem might be solved. Remember the cursor (planner) problem: it might
be solved also this way.

In Linux, on my personal tests at home, memory crashing has happened in
700 meg with psqlodbc, with a simple connection,
with one ODBC statement, with "SELECT * from test1" with about 10
million rows. I don't know wether the crashing happens in 1 million rows
also. I was able to reduce the memory footprint
into max 50 meg, with the "fetch cursor" feature with psqlodbc.
Only that way you are able to process large query results with psqlodbc.

A commercial ODBC driver might not use the cursor feature while being
able to keep in the 50 meg range. Then the PostgreSQL backend optimizes
the queries better:

backend thinks, that with cursors you fetch only about 1% of the results.

I hope, that these details and solution suggestions
help you to overcome the problems.
I hope, that your customers will understand and forgive you.
Nobody of us are perfect, you know. Software programs do have
bugs, you know. Even Windows is not always stable, even
though it is a rather old program.

I am sure, that you try your best to overcome the problems,
until they are solved.

Marko Ristola

Or then
ODBC driver needs 500 meg for further

Unfortunately there is no such thing in the 32 bit address space,
because Win 2k is unable to deliver so big memory buffer.

Personally I think, that this is the problem, you are facing.

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
>
>

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
>
>

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
>
>