[mapserver-users] postgis - idle in transaction
Dave Fuhry
dfuhry at gmail.com
Thu May 22 10:01:47 PDT 2008
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
>> >
>>
>
More information about the MapServer-users
mailing list