Thread: removing idle connections

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

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.

On Tue, 19 Oct 2004 19:24:23 +0100, Gary Doades <gpd@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

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.

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