Re: Priorities for users or queries? - Mailing list pgsql-general

Fair enough,  thanks for the clarification.

Benjamin

Jan Wieck wrote:
> On 2/16/2007 4:56 PM, Benjamin Arai wrote:
>> 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?
>
> It doesn't cause any deadlock by itself. Although the longer one holds
> one lock, before attempting to acquire another, the higher the risk
> someone else grabs that and tries visa versa. So if there is a risk of
> deadlocks due to the access pattern of your application, then slowing
> down the updating processes will increase the risk of it to happen.
>
>
> Jan
>
>>
>> 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
>>>
>>>
>
>