X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9446852A01 for ; Thu, 21 Jul 2005 12:19:00 -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 86940-03 for ; Thu, 21 Jul 2005 15:18:56 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id BBAA4529F5 for ; Thu, 21 Jul 2005 12:18:54 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j6LFICGT018535 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 21 Jul 2005 09:18:15 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j6LFIB0s000623; Thu, 21 Jul 2005 09:18:11 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j6LFIBwX000622; Thu, 21 Jul 2005 09:18:11 -0600 (MDT) (envelope-from mfuhr) Date: Thu, 21 Jul 2005 09:18:11 -0600 From: Michael Fuhr To: Erik Wasser Cc: pgsql-sql@postgresql.org Subject: Re: Are long term never commited SELECT statements are a problem? Message-ID: <20050721151811.GA513@winnie.fuhr.org> References: <200507211557.58822.erik.wasser@iquer.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200507211557.58822.erik.wasser@iquer.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/181 X-Sequence-Number: 22159 On Thu, Jul 21, 2005 at 03:57:56PM +0200, Erik Wasser wrote: > Now I've got here a blocking problem. Severel SQL statements (like > renaming a field or UPDATE of a field) are blocked until I kill a > certain task. This task DOES only the INSERTS and UPDATES with a > transaction and the SELECT statements are not within an transaction. If you set AutoCommit to 0 then all statements are inside a transaction. As you've discovered, SELECT acquires certain locks that persist for the duration of the transaction, so you must commit or roll back the transaction to release those locks (read up on transaction theory to learn more about the rationale for this). -- Michael Fuhr http://www.fuhr.org/~mfuhr/