Re: Predicate locking

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.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate