Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id).

Clients compute new intervals for one variable and store them in that table. To preserve consistency (for a variable_id I don't want intervals to overlap), clients first check (select) what intervals we have and then insert, delete and/or update accordingly (or do nothing).

If two clients do that simultaneously there is of course a risk that I insert overlapping intervals, a thing I don't want.

Well actually my question is simple: what should I do?

It seems that a SELECT .. FOR UPDATE doesn't work, because this locks existing rows, and that's not enough for me, as rows may be inserted. I'd rather need the result of "SELECT * FROM intervals WHERE variable_id = 1234" to be "safe" in some way.

I have implemented a solution that uses SERIALIZABLE transactions. The result is that if two clients enter this select/update-delete-insert transaction, one will fail (could not serialize access due to read/write dependencies among transactions or could not serialize access due to concurrent update), in which case I just have to run again my select/update-delete-insert logic to try to insert more appropriate intervals.

The issue here is that the serializable transaction seems to lock the entire table. But if a client wants to update variable 1234's intervals, while another one wants to update variables 4321's, there should be no problem, both transactions should be able to run happily.

I'm no SQL pro and can't even decide whether this is a simple classic sql question or a difficult issue.

Some remarks :

  • PostgreSQL 9.1.1
  • There's actually a lot more going in the transaction. Not only I update the intervals but I also insert corresponding variables' values in another table. Intervals in the intervals table are the intervals on which we have the variable's values. However I don't think this interferes with the issue described above.
share|improve this question
    
Please upgrade to the latest bug-fix minor release of PostgreSQL. postgresql.org/support/versioning One of the bugs fixed since 9.1.1 is related to the new serializable transaction behavior. –  kgrittn Apr 15 '12 at 15:48
add comment

1 Answer

Regarding the SERIALIZABLE transactions, be sure to pay attention to the "for optimal performance" tips near the bottom of this section: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html#XACT-SERIALIZABLE These can reduce your rollback rates quite significantly.

That said, and not to discourage use of the SERIALIZABLE transaction isolation level, what you describe sounds like exactly the sort of problem that EXCLUSION CONSTRAINTs were designed to solve. Where there is a declarative constraint to enforce a business rule, that is almost always better than using imperative coding (in triggers or application code).

http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

To use this feature with dates you would currently need to use the TEMPORAL module:

http://pgfoundry.org/projects/temporal/

You will probably want to migrate TEMPORAL columns to a RANGE when you move to PostgreSQL version 9.2 (expected to be released this summer). The 9.2 RANGE feature, the TEMPORAL data type, and the EXCLUSION CONSTRAINT feature were all the work Jeff Davis to address problems like that posed in the question. They were designed to work together.

Full disclosure: The SERIALIZABLE transaction behavior in 9.1 was developed by myself and Dan R.K. Ports of MIT.

share|improve this answer
    
Well then, first of all thanks for your work on PostgreSQL! I didn't know the EXCLUSION constraints and hadn't thought of enforcing the constraint at database level. It's interesting. However, I haven't dug into temporal data type, but it seems that I'd have a problem in specifying a constraint on both the interval and the variable_id, wouldn't I? I don't want all intervals to be distinct, but rather all intervals of a variable to be distinct. (to be cont.) –  Arthur Apr 15 '12 at 16:57
    
This article uses EXCLUDE constraints and the "2D-ness" of box type for a similar problem, in a convoluted but interesting way. I'll give more thoughts into that, and maybe keep my SERIALIZABLE solution for now and wait for 9.2. Anyway, thanks again! –  Arthur Apr 15 '12 at 16:58
    
If the SERIALIZABLE transaction isolation level is working well enough, it makes a lot of sense to wait for 9.2 to be released and stable before considering a switch to EXCLUSION CONSTRAINTs. If nothing else, they should perform better (as built-in handling for a special case usually will), but maybe not by enough to matter a lot, depending on the workload and tuning. –  kgrittn Apr 15 '12 at 18:48
    
This technique can, indeed, look at more than just the overlapping range. I attended a couple presentations by Jeff at conferences while he was developing the feature, and one example he used was scheduling rooms for non-overlapping use, which seems pretty close to the use-case in the question, logically. Slides from one of those presentations are here: postgresqlconference.org/sites/default/files/… –  kgrittn Apr 15 '12 at 18:49
    
Thanks for your answers on the site @kgrittn, and for the great work on SSI. I'm an Oracle DBA but a huge fan of postgres and very interested to hear you are working on declarative materialized views. Do drop into the chat room sometime and say hello if you feel inclined to do so :-) Incidentally I'm looking into setting up a facility for getting an RSS feed of interesting postgres questions for depesz in case you are interested. –  Jack Douglas Apr 20 '12 at 6:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.