Thread: psqlodbc 08.00.0004 - questions

psqlodbc 08.00.0004 - questions

From
Együd Csaba
Date:
Hi,
1. I examined the psqlodbc_xxxx.log and found that despite of the 13
parallel established connections, only the last (conn = 1389704) connection
is used for inserting. The application which connects to the server via the
driver uses 13 parallel threads connecting thread by thread.

2. In which circumstances can occure that ODBC driver refuses a connection
from the server. I ran into a problem, where the server reported that the
client actively refused the connection. What does it mean?

Sometimes there are 54 or more established connection to the same server,
while the software uses only 13. I think many of them must be dead but kept
alive connections. How long the Windows keeps these inactive connections
alive? How can I configure it?

Thank you,
-- Csaba Együd


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 2005.01.17.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 2005.01.17.


Re: psqlodbc 08.00.0004 - questions

From
"Merlin Moncure"
Date:
> Hi,
> 1. I examined the psqlodbc_xxxx.log and found that despite of the 13
> parallel established connections, only the last (conn = 1389704)
> connection
> is used for inserting. The application which connects to the server via
> the
> driver uses 13 parallel threads connecting thread by thread.

Question: what language are using to connect to the server?  I can't directly answer your questions, but you may want
toconsider using the libpq library for more detailed control over your connection objects.   

> 2. In which circumstances can occure that ODBC driver refuses a connection
> from the server. I ran into a problem, where the server reported that the
> client actively refused the connection. What does it mean?

Probably you are hitting your connection limit set in postgresql.conf.  Monitor your server log for more info.

> Sometimes there are 54 or more established connection to the same server,
> while the software uses only 13. I think many of them must be dead but
> kept
> alive connections. How long the Windows keeps these inactive connections
> alive? How can I configure it?

The server will keep a connection open as long as the connection is maintained by the client.  Killing the process that
ownsthe connection is a guaranteed way to close them.  Unfortunately, in a threaded app killing the thread is not
enoughto guarantee closure of the connection owned by the thread (unless it is the last thread, then the process will
terminate).

As far as I know, it is not possible to configure postgres to kill connections that have not been used for a while but
itis not all that difficult to rig up a procedure that could do it (if you are keeping statistics).   

In these types of cases it is hard to determine if some of your problems are not on your end...maybe a better
descriptionof your problem (language, requirements, etc.) are in order. 

Merlin

Re: psqlodbc 08.00.0004 - questions

From
Együd Csaba
Date:
Hi Merlin,
> > Hi,
> > 1. I examined the psqlodbc_xxxx.log and found that despite of the 13
> > parallel established connections, only the last (conn = 1389704)
> > connection is used for inserting. The application which connects to
> > the server via the driver uses 13 parallel threads connecting thread
> > by thread.
>
> Question: what language are using to connect to the server?  I can't
directly answer your questions, but you may want to consider using the libpq
library for more detailed control over your connection objects.

CSE: It is a third party paaqlication (as far as I know MFC, may be c++). I
cannot change it, but I could ask the developer to do some modifications if
I know which part sholud be modified.

>
> > 2. In which circumstances can occure that ODBC driver refuses a
> > connection from the server. I ran into a problem, where the server
> > reported that the client actively refused the connection. What does it
mean?
>
> Probably you are hitting your connection limit set in postgresql.conf.
Monitor your server log for more info.
CSE: The server log reports that the CLIENT actively refues the connection.
Actually it works for a while (sometimes for 2 minutes sometimes for hours).
The server is configured to accept 100 connections. This app - in principal
- needs only 13. Other system components need 5-6 connections. The remaining
~80 connections could be enough for the clients. Sometimes the server can't
accept any further attemtions because of too many connections. But What can
I do to avoid this problem?

>
> > Sometimes there are 54 or more established connection to the same
> > server, while the software uses only 13. I think many of them must be
> > dead but kept alive connections. How long the Windows keeps these
> > inactive connections alive? How can I configure it?
>
> The server will keep a connection open as long as the connection is
maintained by the client.  Killing the process that owns the connection is a
guaranteed way to close them.  Unfortunately, in a threaded app killing the
thread is not > enough to guarantee closure of the connection owned by the
thread (unless it is the last thread, then the process will terminate).

CSE: I regulary kill the process from the Task Manager window. But the
connections are kept alive. (???)

>
> As far as I know, it is not possible to configure postgres to kill
connections that have not been used for a while but it is not all that
difficult to rig up a procedure that could do it (if you are keeping
statistics).
>
> In these types of cases it is hard to determine if some of your problems
are not on your end...maybe a better description of your problem (language,
requirements, etc.) are in order.
>
> Merlin

1. The client starts 13 threads.
2. Each thread establishes it's own odbc connection.
3. Each thread performs INSERT INTO queries 4-54 times per minute
4. Sometimes the ODBC log shows some syntax errors. After the first syntax
error the whole client process eather stops inserting rows into the database
or stops working. Sometimes it stucks into the Task Manager sometimes
disappears.
5. In some cases the server log reports that it can not conect to the client
because of an active refusion.
6. After several restarts of the client software the server stops answering
because of the too many connections.
7. After several hours the server is available again. But this happens after
several client restart. In most cases not this is the problem.

-- Csaba



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 2005.01.17.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 2005.01.17.