Re: Need help writing exclusion constraint - Mailing list pgsql-general

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