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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > wrote: >> > More pain? >> > >> > ---------- Forwarded message ---------- >> > From: Ivan Mincik <[EMAIL PROTECTED]> >> > Date: Mon, May 19, 2008 at 10:46 AM >> > Subject: [mapserver-users] postgis - idle in transaction >> > To: [email protected] >> > >> > >> > 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 >> > [email protected] >> > http://lists.osgeo.org/mailman/listinfo/mapserver-users > _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
