pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Benjamin Arai <benjamin@araisoft.com>
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Adam Rich <adam.r@sbcglobal.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Priorities for users or queries?
Date: Fri, 16 Feb 2007 13:56:08 -0800
Message-ID: <45D62878.60406@araisoft.com> (raw)
In-Reply-To: <45D625B4.5040008@Yahoo.com>
References: <000001c74da2$a9a4a990$6400a8c0@dualcore>
	<45CEE33B.8020303@hagander.net>
	<45CF5A19.1010101@araisoft.com>
	<45D625B4.5040008@Yahoo.com>

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
>
>



view thread (30+ messages)  latest in thread

Message-ID: <45D62878.60406@araisoft.com>
Permalink:  ../45D62878.60406@araisoft.com/
Also on:    postgresql.org/message-id/45D62878.60406@araisoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: benjamin@araisoft.com, JanWieck@Yahoo.com, magnus@hagander.net, adam.r@sbcglobal.net, pgsql-general@postgresql.org
  Subject: Re: Priorities for users or queries?
  In-Reply-To: <45D62878.60406@araisoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox