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.
  • 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, 2012 at 15:48

1 Answer 1


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).



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


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.

  • 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, 2012 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, 2012 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, 2012 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, 2012 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. Apr 20, 2012 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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