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 <[EMAIL PROTECTED]> 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 <[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
