From: | Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> |
---|---|
To: | Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Predicate locking |
Date: | 2011-04-27 09:38:14 |
Message-ID: | 4DB7E406.6040100@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2011-04-27 10:47:32 | Re: alpha5 |
Previous Message | Vlad Arkhipov | 2011-04-27 09:24:35 | Re: Predicate locking |