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 0CF43D1B20E for ; Fri, 9 Jul 2004 11:58:52 -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 25118-09 for ; Fri, 9 Jul 2004 14:58:51 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 30676D1B20D for ; Fri, 9 Jul 2004 11:58:46 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i69Ewji7081759 for ; Fri, 9 Jul 2004 14:58:45 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i69EYr0L077068 for pgsql-admin@postgresql.org; Fri, 9 Jul 2004 14:34:53 GMT From: "Konstantin Pelepelin" X-Newsgroups: comp.databases.postgresql.admin Subject: are there ways for 'idle timeout'? Date: Fri, 09 Jul 2004 18:36:08 +0400 Organization: DTF Lines: 26 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; delsp=yes; charset=koi8-r Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Opera M2/7.51 (Linux, build 689) To: pgsql-admin@postgresql.org 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/118 X-Sequence-Number: 14186 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