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<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/
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
