Re: Predicate locking

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/

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com