Re: Predicate locking - Mailing list pgsql-hackers

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@dc.baikal.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.