Re: Predicate locking

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:http://wiki.postgresql.org/wiki/Serializable>
>>>>>
>>>>> 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:
>>>
>>> http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION)
>>>
>>>
>>> See also Depesz's blog post for a specific example on how to use it
>>> for time ranges:
>>>
>>> http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/
>>>
>>>
>>> And Jeff Davis's blog post that uses the period data type instead of
>>> the hack to represent time ranges as boxes:
>>>
>>> http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/
>>>
>>>
>> 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.