[mapserver-users] postgis - idle in transaction

[Dave Fuhry]

Ivan,

If you shutdown Apache or whatever is running FastCGI, then I assume
mapserver will close those connections.

Pooled mapserver connections certainly are idle in a transaction.
mapserver issues a BEGIN (transaction) when the connection is made,
because the binary cursor queries mapserver issues must be in a
transaction.

If it's a problem to be leaving idle connections in transactions, we
can change things to issue BEGIN right before the DECLARE BINARY
CURSOR query, and ROLLBACK right after the CLOSE CURSOR.  That's two
more database calls though; are they necessary?

The queries never modify data, so the transactions won't be holding
any sort of write locks.  It's very normal for an app to hold a pool
of idle connections to a database.  Is there a reason it's worse when
each of those connections is inside a transaction?

Thanks,

Dave

On Mon, May 19, 2008 at 3:09 PM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> More pain?
>
> ---------- Forwarded message ----------
> From: Ivan Mincik <ivan.mincik at gmail.com>
> Date: Mon, May 19, 2008 at 10:46 AM
> Subject: [mapserver-users] postgis - idle in transaction
> To: mapserver-users at lists.osgeo.org
>
>
> Hi,
> I have started using p.mapper WebGIS client with
> PROCESSING "CLOSE_CONNECTION=DEFER"
> parameter for all postgis layers. (using the latest version of mapserver
> 5.0.x and patch for the issue with CLOSE_CONNECTION=DEFER).
>
> In htop I see lot of connections "postgres: user database 127.0.0.1(47132)
> idle in transaction" which will remain until the next restart of database
> server.
>
> Has anybody the same problem ?
>
> Thanks Ivan
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>