Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CC7329FB406 for ; Fri, 16 Feb 2007 17:56:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 45050-09 for ; Fri, 16 Feb 2007 17:56:16 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sumo.dreamhost.com (sumo.dreamhost.com [66.33.216.29]) by postgresql.org (Postfix) with ESMTP id 68D979FB409 for ; Fri, 16 Feb 2007 17:56:19 -0400 (AST) Received: from spunkymail-a2.g.dreamhost.com (sd-green-bigip-145.dreamhost.com [208.97.132.145]) by sumo.dreamhost.com (Postfix) with ESMTP id 0B59D17B9BA for ; Fri, 16 Feb 2007 13:56:16 -0800 (PST) Received: from benjamin-arais-computer.local (wg05.ucr.edu [138.23.2.34]) by spunkymail-a2.g.dreamhost.com (Postfix) with ESMTP id 298EB870EF; Fri, 16 Feb 2007 13:56:07 -0800 (PST) Message-ID: <45D62878.60406@araisoft.com> Date: Fri, 16 Feb 2007 13:56:08 -0800 From: Benjamin Arai Organization: Araisoft User-Agent: Thunderbird 2.0b2 (Macintosh/20070116) MIME-Version: 1.0 To: Jan Wieck Cc: Magnus Hagander , Adam Rich , pgsql-general@postgresql.org Subject: Re: Priorities for users or queries? References: <000001c74da2$a9a4a990$6400a8c0@dualcore> <45CEE33B.8020303@hagander.net> <45CF5A19.1010101@araisoft.com> <45D625B4.5040008@Yahoo.com> In-Reply-To: <45D625B4.5040008@Yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/905 X-Sequence-Number: 109395 Hi Jan, That makes sense. Does that mean that a low-priority "road-block" can cause a deadlock or just an very long one lock? Benjamin Jan Wieck wrote: > On 2/11/2007 1:02 PM, Benjamin Arai wrote: >> Hi Magnus, >> >> Think this can be avoided as long the the queries executed on the >> lower priority process never lock anything important. In my case, I >> would alway be doing inserts with the lower priority process, so >> inversion should never occur. On the other hand if some lock occur >> somewhere else specific to Postgres then there may be an issue. Are >> there any other tables locked by the the Postgres process other than >> those locks explicitly set by the query? > > If you assume that the logical row level locks, placed by such low > priority "road-block", would be the important thing to watch out for, > you are quite wrong. Although Postgres appears to avoid blocking > readers by concurrent updates using MVCC, this isn't entirely true. > The moment one updating backend needs to scribble around in any heap > or index block, it needs an exclusive lock on that block until it is > done with that. It will not hold that block level lock until the end > of its transaction, but it needs to hold it until the block is in a > consistent state again. That means that the lower the priority of > those updating processes, the more exclusively locked shared buffers > you will have in the system, with the locking processes currently not > getting the CPU because of their low priority. > > > Jan > >> >> Benjamin >> >> Magnus Hagander wrote: >>> Most likely, you do not want to do this. You *can* do it, but you are >>> quite likely to suffer from priority inversion >>> (http://en.wikipedia.org/wiki/Priority_inversion) >>> >>> //Magnus >>> >>> >>> Adam Rich wrote: >>> >>>> There is a function pg_backend_pid() that will return the PID for >>>> the current session. You could call this from your updating app >>>> to get a pid to feed to the NICE command. >>>> >>>> >>>> -----Original Message----- >>>> From: pgsql-general-owner@postgresql.org >>>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Benjamin Arai >>>> Sent: Saturday, February 10, 2007 6:56 PM >>>> To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org >>>> Subject: [GENERAL] Priorities for users or queries? >>>> >>>> >>>> Hi, >>>> >>>> Is there a way to give priorities to queries or users? Something >>>> similar to NICE in Linux. My goal is to give the updating >>>> (backend) application a very low priority and give the web >>>> application a high priority to avoid disturbing the user experience. >>>> >>>> Thanks in advance! >>>> >>>> Benjamin >>>> >>>> >>>> ---------------------------(end of >>>> broadcast)--------------------------- >>>> TIP 4: Have you searched our list archives? >>>> >>>> http://archives.postgresql.org/ >>>> >>>> >>>> ---------------------------(end of >>>> broadcast)--------------------------- >>>> TIP 3: Have you checked our extensive FAQ? >>>> >>>> http://www.postgresql.org/docs/faq >>>> >>> >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq > >