[mapserver-users] postgis - idle in transaction

[Dave Fuhry]

Ivan,

   Try applying the patch at this new ticket:
http://trac.osgeo.org/mapserver/ticket/2626

   That should allow you to ALTER TABLE when connections are idle.

Thanks,

Dave

On Wed, May 21, 2008 at 4:51 PM, Ivan Mincik <ivan.mincik at gmail.com> wrote:
> Thanks Dave.
> I will try the patch instantly as You prepare it.
> Ivan
>
> On Wednesday 21 May 2008 21:46, you wrote:
>> 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
>> >
>>
>