[mapserver-users] postgis - idle in transaction

[Dave Fuhry]

Ivan,

   Well, I guess problems like that are enough to convince me that we
need to move the BEGIN right before the cursor query, and to
explicitly ROLLBACK right after.

   There is no real reason that our readonly transactions need to hold
locks on those tables after their queries are complete, but since they
feel compelled to, we'd best hold them for a minimum of time.

   Paul's inclination to drop BINARY CURSORs for a single SELECT
statement is looking ever-more attractive, as the steps to retrieve
postgis data will now be:

BEGIN
DECLARE mycursor BINARY CURSOR FOR ...
FETCH ALL IN mycursor
CLOSE mycursor
ROLLBACK

   I think we could fold the first two lines into "BEGIN; DECLARE
mycursor BINARY CURSOR FOR ..." and the last two into "CLOSE mycursor;
ROLLBACK" to keep latency & database calls a bit lower.  Seems pretty
safe but I'm not entirely convinced, so maybe as a followup patch.

   I'll work on a patch to change query behavior to the above.  Maybe
by late this evening.

Thanks,

Dave

On Wed, May 21, 2008 at 5:14 AM, Ivan Mincik <ivan.mincik at gmail.com> wrote:
> Thank for the reply Dave,
> the reason I wrote this post is that I am not sure if this behavior is NORMAL
> or NOT.
>
> ONE MORE QUESTION  to ask: When I try to add column to database ( ALTER TABLE
> rieky ADD COLUMN test varchar(30);) the query will not finish  when  "idle in
> transaction" processes are existing. I have to stop query by Ctrl-C.
>
> So, when I want to add column, I have to stop apache to kill these processes
> and than run query.
> Is is it normal because some kind of lock?
>
>
> This is  "ps -aux" when NO user is connected.
>
> www-data 13072  0.0  7.5 156444 19892 ?        S    May20
> 0:01 /usr/sbin/apache2 -k start
>
> postgres 13074  0.0  2.1  41256  5516 ?        S    May20   0:00 postgres: map
> map 127.0.0.1(48763) idle in transaction
>
> postgres 13075  0.0  3.7  51640  9756 ?        S    May20   0:00 postgres: map
> map 127.0.0.1(48764) idle in transaction
>
> postgres 13076  0.0  3.7  51640  9776 ?        S    May20   0:00 postgres: map
> map 127.0.0.1(48765) idle in transaction
>
> postgres 13077  0.0  3.8  51636 10132 ?        S    May20   0:01 postgres: map
> map 127.0.0.1(48766) idle in transaction
>
> postgres 13078  0.0  3.8  51636 10088 ?        S    May20   0:00 postgres: map
> map 127.0.0.1(48767) idle in transaction
>
> postgres 15940  0.0  3.5  51516  9288 ?        S    09:33   0:00 postgres: map
> map 127.0.0.1(39643) idle in transaction
>
> postgres 15963  0.0  3.4  51444  8972 ?        S    09:40   0:00 postgres: map
> map 127.0.0.1(57171) idle in transaction
>
>
>> Ivan,
>>
>> If you shutdown Apache or whatever is running FastCGI, then I assume
>> mapserver will close those connections.
> Yes, after restarting apache they will disappear.
>>
>> 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
>