X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 881F9D1B18C for ; Sat, 10 Jul 2004 00:30:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32154-06 for ; Sat, 10 Jul 2004 03:30:37 +0000 (GMT) Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.198.39]) by svr1.postgresql.org (Postfix) with ESMTP id 37C85D1B269 for ; Sat, 10 Jul 2004 00:30:30 -0300 (ADT) Received: from localhost.localdomain (c-67-162-89-61.client.comcast.net[67.162.89.61]) by comcast.net (rwcrmhc13) with ESMTP id <2004071003303401500i1dd1e>; Sat, 10 Jul 2004 03:30:35 +0000 Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.12.11/8.12.11) with ESMTP id i6A3TCme010891; Fri, 9 Jul 2004 22:29:12 -0500 Received: (from spike@localhost) by localhost.localdomain (8.12.11/8.12.11/Submit) id i6A3TBwV010888; Fri, 9 Jul 2004 22:29:11 -0500 X-Authentication-Warning: localhost.localdomain: spike set sender to mike@thegodshalls.com using -f Subject: Re: are there ways for 'idle timeout'? From: mike g Reply-To: mike@thegodshalls.com To: Konstantin Pelepelin Cc: pgsql-admin@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1089430151.10763.17.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 (1.4.5-7) Date: Fri, 09 Jul 2004 22:29:11 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/126 X-Sequence-Number: 14194 The best solution would be to fix the web app but.... Could you set the deadlock_timeout in postgresql.conf? According to documentation that will kill one of the transactions that has issued the lock on the table or row? On Fri, 2004-07-09 at 09:36, Konstantin Pelepelin wrote: > Hi! > > In situations where client application is unstable there is very big > chance that it will enter transaction, acquire very restrictive locks and > then hang. This means: connection is not lost, but nothing happens and any > transaction in conflict with those locks will hang too. > statement_timeout can help to detect this situation, but not to solve, > when most of applications fail. Currently the only way I see to solve is > to kill locked postgres backend, or (more difficult) find hanging client > among hundreds of similar. > I work with web-application (Apache-mod_php-Postgres), where PHP hangs > every 100000 requests or so, which means from a day to some weeks between > whole system hangs. > I could set up a cron job which will kill every postgres backend "idle in > transaction" which was started more than 10 minutes ago, but I can have > accident problems with some long-running tasks. > The best solution I see is having an (per session) > idle_transaction_timeout or idle_connection_timeout setting. > But there is no such thing in postgres! > Scanning through list I've not found any workaround. > Looking into TODO I don't see any plans for it. > Is it too difficult implementing or I've missed something or are there > workarounds? > > Hoping on your responce, > Konstantin Pelepelin > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match