Re: Predicate locking
От | Vlad Arkhipov |
---|---|
Тема | Re: Predicate locking |
Дата | |
Msg-id | 4DBA2C52.3030506@dc.baikal.ru обсуждение исходный текст |
Ответ на | Re: Predicate locking (David Fetter <david@fetter.org>) |
Список | 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@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. >> > 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.
В списке pgsql-hackers по дате отправления: