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 5CD5AD1B1A9 for ; Mon, 12 Jul 2004 06:25: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 24168-03 for ; Mon, 12 Jul 2004 09:25:35 +0000 (GMT) Received: from expo.dtf.ru (unknown [212.113.96.102]) by svr1.postgresql.org (Postfix) with ESMTP id 53708D1B1A5 for ; Mon, 12 Jul 2004 06:25:25 -0300 (ADT) Received: from cathost.of.dtf.ru (cathost.of.dtf.ru [212.113.112.5]) by expo.dtf.ru (8.11.6/8.11.6) with ESMTP id i6C9Q5H17797; Mon, 12 Jul 2004 13:26:05 +0400 Received: from localhost (localhost [127.0.0.1]) by cathost.of.dtf.ru (8.11.6/8.11.6) with ESMTP id i6C9Qga02448; Mon, 12 Jul 2004 13:26:42 +0400 From: Konstantin Pelepelin To: mike@thegodshalls.com Subject: Re: are there ways for 'idle timeout'? Date: Mon, 12 Jul 2004 13:26:41 +0400 User-Agent: KMail/1.5.1 Cc: pgsql-admin@postgresql.org References: <1089430151.10763.17.camel@localhost.localdomain> In-Reply-To: <1089430151.10763.17.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200407121326.41770.checat@pochtamt.ru> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.4 tagged_above=0.0 required=5.0 tests=DNS_FROM_RFCI_DSN X-Spam-Level: * X-Archive-Number: 200407/133 X-Sequence-Number: 14201 Hi! > The best solution would be to fix the web app but... ...but it's not a problem in my app, but in development platform (PHP), which is quite buggy. > Could you set the deadlock_timeout in postgresql.conf? It is set. Alas, it doesn't help, because there is no deadlock condition. (Deadlock means two transactions cannot continue because each tries to set lock conflicting with already acquired by another, mutually.) Thank you, Konstantin Pelepelin > > 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?