On Wed, 2011-01-19 at 10:15 -0500, Daniel Popowich wrote: > Anyway...Jeff, all your answers depend on using new features in 9.0. > What would you recommend for folk still using 8.4? Without 9.0 > exclusion constraints, what else can you do besides using functions in > check constraints (or triggers) with appropriate locking (at some > level of the overall application architecture). There are several approaches, but all of them leave something to be desired, of course. I break the alternative solutions into 4 categories: 1. Full table lock -- instead of using a CHECK constraint, use a trigger and acquire a full table lock. The obvious problem here is the contention over that lock, so transactions will need to be kept short. Performance with this approach will not be very good, but perhaps that's OK in some situations. 2. What I call "quantization". That is, choose a size, say one hour, and assume that "1:00" really means "1:00 - 2:00". Then you can use a UNIQUE index. You have to align everything on the hour exactly (you can't do 1:30-2:30, for instance), and longer reservations require multiple entries. Choosing an appropriate chunk size is difficult, because if it's too big then it makes the application overly strict (and imposes inconveniences on your organization); but if you choose a size that is too small, it requires many entries for a single reservation (if you choose one minute, then a one-hour reservation requires 60 rows). These drawbacks are acceptable for some organizations, but not all. 3. Complex procedural code can be used. For instance, you might have a separate table (call it "my_locks") of rows that exist just for row-level locks. One row would represent 1:00 - 2:00, another 2:00 - 3:00, etc. And when you go to insert into the main table (call it "reservations"), you take a row-level lock on every row in my_locks that overlaps with the time you are inserting. So, if you are inserting 10:30 - 11:30 into the reservations table, you would take a lock on the rows 10:00 - 11:00 and 11:00 - 12:00 in your my_locks table. This effectively partitions your lock space, so that a reservation for 1:30 - 2:30 won't have to wait for a reservation between 10:30 and 11:30. There are other ideas along these lines as well, this is just an example of how adding complexity can help. Be careful though, the complexity explodes pretty quickly, and there are a lot of hidden performance traps. 4. You can work outside of the transactional system. Record both reservations, and check later for conflicts. The problem here is what to do when you find one. If you want to undo one reservation, and it was part of a larger transaction, you have to figure out how to undo the whole transaction. And you need to keep a log of which transactions need to be checked, so that if there is a crash you don't lose track and leave the conflicting reservations in there. As you can see, none of these are ideal. But, if you run into a specific problem, you can usually pick one of these approaches and make it work with careful determination. Exclusion constraints are much easier, however ;) Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
