Skip site navigation (1) Skip section navigation (2)

Re: Predicate locking

From: Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Predicate locking
Date: 2011-04-29 03:11:14
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
28.04.2011 21:36, David Fetter пишет:
> On Thu, Apr 28, 2011 at 12:07:34PM +0900, Vlad Arkhipov wrote:
>> 27.04.2011 18:38, Heikki Linnakangas пишет:
>>> On 27.04.2011 12:24, Vlad Arkhipov wrote:
>>>> 27.04.2011 17:45, Nicolas Barbier:
>>>>> 2011/4/27 Vlad Arkhipov<arhipov(at)dc(dot)baikal(dot)ru>:
>>>>>> I'm currently need predicate locking in the project, so there are two
>>>>>> ways
>>>>>> to get it by now: implement it by creating special database records
>>>>>> to lock
>>>>>> with SELECT FOR UPDATE or wait while they will be implemented in
>>>>>> Postgres
>>>>>> core. Is there something like predicate locking on the TODO list
>>>>>> currently?
>>>>> I assume you want ("real", as opposed to what is in<  9.1 now)
>>>>> SERIALIZABLE transactions, in which case you could check:
>>>>> <URL:>
>>>>> Nicolas
>>>> Not sure about the whole transaction, I think it degrades the
>>>> performance too much as transactions access many tables. Just wanted
>>>> SELECT FOR UPDATE to prevent inserting records into a table with the
>>>> specified condition. It seems to be very typical situation when you have
>>>> a table like
>>>> CREATE TABLE timetable (start_ts TIMESTAMP, end_ts TIMESTAMP)
>>>> and before insertion in this table want to guarantee that there is no
>>>> overlapped time intervals there. So, first you need to lock the range in
>>>> the table, then to check if there are any records in this range.
>>>> In my case this table is the only for which I need such kind of locking.
>>> You can do that with exclusion constraints:
>>> See also Depesz's blog post for a specific example on how to use it
>>> for time ranges:
>>> And Jeff Davis's blog post that uses the period data type instead of
>>> the hack to represent time ranges as boxes:
>> Exclusion constraints works only in simple cases. I need to check a
>> great amount of business rules to assure that the insertion is
>> possible. For example,
>> for a table with columns (start_ts TIMESTAMP, end_ts TIMESTAMP, room
>> BIGINT, visitor BIGINT, service BIGINT) it's not possible to have
>> overlapped intervals
>> for the same time and room, but different visitors. So, in terms of
>> exclusion constraints I need something like:
>> room WITH =,
>> visitor WITH<>,
>> (start_ts, end_ts) WITH&&
>> which seems to be impossible. Predicate locking provides more
>> flexible way to solve this problem.
> Did you actually try it?  It works just fine with a timestamp range.
> Cheers,
> David.
Yes. It does not work on 9.0 when I add 'visitor WITH <>'.
ERROR:  failed to re-find tuple within index "overlapping"
HINT:  This may be because of a non-immutable index expression.

But even if it would work it would not help me anyways. Because my 
constraint is much more complex and depends on other tables, I cannot 
express it in terms of exclusion constraints.

In response to

pgsql-hackers by date

Next:From: Ashutosh BapatDate: 2011-04-29 06:00:54
Subject: Re: What would AggrefExprState nodes' args contain?
Previous:From: Greg SmithDate: 2011-04-29 00:14:16
Subject: Re: VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group