reservation overlap constraint checking?

This must be a classic SQL/RDBMS design problem yet I can't find any good
"cookbook" descriptions on how best to do it.

The problem is simple, there are a bunch of resources that can be reserved for
arbitrary time periods and I have to make sure there are no overlapping
reservations (think hotel rooms, but in this case it is an LCD projector):

grot=> \d go_reserve
Table "public.go_reserve"
Column | Type | Modifiers
------------+--------------------------+---------------------------------
resource | bigint |
username | text |
start_time | timestamp with time zone | not null default now()
end_time | timestamp with time zone | not null default now()
status | text | not null default 'ACTIVE'::text
inserted | timestamp with time zone | not null default now()
updated | timestamp with time zone | not null default now()
updatedby | text | not null
Check constraints:
"end_gt_start" CHECK (end_time > start_time)
Foreign-key constraints:
"$1" FOREIGN KEY (resource) REFERENCES go_resource(id)
"$2" FOREIGN KEY (username) REFERENCES bloki_user(username)

grot=> select * from go_reserve ;
resource | username | start_time | end_time | status | inserted | updated |
updatedby
----------+----------+-------------------------------+-------------------------------+--------+-------------------------------+-------------------------------+-----------
1 | aditya | 2005-05-12 13:42:50.158781-04 | 2005-051 313:42:50.158781-04 | ACTIVE | 2005-05-19 13:42:50.158781-04 | 2005-05-1913:42:50.158781-04 | aditya
1 | aditya | 2005-05-19 10:43:20.889513-04 | 2005-05-1911:43:20.889513-04 | ACTIVE | 2005-05-19 13:43:20.889513-04 | 2005-05-1913:43:20.889513-04 | aditya
1 | aditya | 2005-05-19 16:43:38.778486-04 | 2005-05-1917:43:38.778486-04 | ACTIVE | 2005-05-19 13:43:38.778486-04 | 2005-05-1913:43:38.778486-04 | aditya
1 | aditya | 2005-05-19 18:43:50.168998-04 | 2005-05-1919:43:50.168998-04 | ACTIVE | 2005-05-19 13:43:50.168998-04 | 2005-05-1913:43:50.168998-04 | aditya
(4 rows)

So postgresql has the "overlaps" operator

http://www.postgresql.org/docs/7.4/static/functions-datetime.html

that works with timestamps, so I can do things like

grot=> select case when ('2005-05-19 18:45:00'::timestamp, '2005-05-19
18:50:00'::timestamp) overlaps (start_time, end_time) then 'unavailable' else
'available' end from go_reserve where resource = 1 and status = 'ACTIVE';
case
-------------
available
available
available
unavailable
(4 rows)

so I can do it such that I run a query first to find if there are any
overlaps, and if that returns no overlaps, then I insert:

grot=> select count(*) from go_reserve where (('2005-05-19
18:41:00'::timestamp, '2005-05-19 18:42:00'::timestamp) overlaps (start_time,
end_time) = true) and resource = 1 and status = 'ACTIVE';
count
-------
0
(1 row)

however, that seems less than elegant because there is time between the check
and the insert for a competing entry to get inserted....so next thing to try
is a constratint:

alter table go_reserve add constraint no_overlaps check (!(select (start_time,
end_time) overlaps (select start_time, end_time from go_reserve where
start_time >= now() and status = 'ACTIVE')));

but postgresql doesn't like selects in a constraint...

clues?

Thanks,
Adi