On 27.04.2011 12:24, Vlad Arkhipov wrote:
27.04.2011 17:45, Nicolas Barbier:
2011/4/27 Vlad Arkhipov<arhi...@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/
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
