removing idle connections

[Josh Close]

Is there a way to remove idle connections? My postgres server is
getting serveral hundred idle connections. It's due to a postgres .NET
provider not closing the connections properly. I don't want to kill
them all, or restart postgres everytime the connections go crazy.

-Josh

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Gary Doades]

On 19 Oct 2004 at 13:00, Josh Close wrote:

Is there a way to remove idle connections? My postgres server is
getting serveral hundred idle connections. It's due to a postgres .NET
provider not closing the connections properly. I don't want to kill
them all, or restart postgres everytime the connections go crazy.

I would have though it would be better to fix the client application. If the
app is not closing connections then you may be leaking handles and
memory.

What .NET provider is this? Are you sure it is not just normal
connection pooling?

Cheers,
Gary.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Josh Close]

On Tue, 19 Oct 2004 19:24:23 +0100, Gary Doades <gp*@gpdnet.co.uk> wrote:

I would have though it would be better to fix the client application. If the
app is not closing connections then you may be leaking handles and
memory.

What .NET provider is this? Are you sure it is not just normal
connection pooling?

Cheers,
Gary.

The provider is corelabs. The programmer that wrote the code says he's
closing the connections, but they aren't actually closing.

Any ideas? Or better yet, do you know of a good postgres .net provider?

-Josh

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Gary Doades]

On 19 Oct 2004 at 13:32, Josh Close wrote:

The provider is corelabs. The programmer that wrote the code says he's
closing the connections, but they aren't actually closing.

Any ideas? Or better yet, do you know of a good postgres .net provider?

Hmm, I've had lots of problems with the Corelabs provider. The open
source provider (Npgsql) available on GBorg is a lot better for most
things.

I reverted to using the ODBC driver for Postgres with the .NET Odbc
provider. Works fine.

With connection pooling enabled you will see several "idle" connections
around waiting to be re-used by a provider connection supplying the
same credentials and options as a "disconnected" pooled-but-idle
connection. If you connect with a different username/password
everytime then this may be a problem, otherwise it is a big performance
gain in the disconnected recordset world of .NET.

Cheers,
Gary.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Gaetano Mendola]

Josh Close wrote:

Is there a way to remove idle connections? My postgres server is
getting serveral hundred idle connections. It's due to a postgres .NET
provider not closing the connections properly. I don't want to kill
them all, or restart postgres everytime the connections go crazy.

I do not think is problem of not close the connections.
I bet the driver is acting like this:
On connection:
. Connect
. start transaction

On Commit:
. commit transaction
. start transaction

On Abort:
. abort transaction
. start transaction

On statemet:
. execute statement
As you can see you are always inside a transaction, idle I mean.
BTW this is the behaviour of python driver PgDB ( I suggest to
use psycopg instead ) and before the 8.0 series the JDBC driver
did the same. The way to solve it is, delay the begin till the first
statement:
On connection:
. Connect

On Commit:
. commit transaction

On Abort:
. abort transaction

On statemet:
. If is the first statement after a connection or a commit or
an abort execute the: start transaction
. execute statement

For rpm mantainer: why do not include the psycopg instead of the actual
python driver ?
Regards
Gaetano Mendola