CREATE TABLE room_reservation (
EXCLUDE USING gist (room WITH =, during WITH &&)
INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with
existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )).
Used to have to rely on the application to do these sorts of constraints.
One of these is legitimately revolutionary, and it isn't covering indexes.
Without PostgreSQL's exclusion constraints this can be solved in various more or less ugly and problem specific ways. Like locking the entire table, allowing overlapping ranges and cleaning them up later, implementing an own locking infrastructure (e.g. have one lock per day or per room), only having one process inserting at the same time, or just ignoring the problem and hoping you wont get any race conditions.
Exclusion constraints is a clean solution to a messy problem.
Say you have a table with a bunch of columns: (column1...column15) and you have an index on (column3, column4) and you run a query like:
select column4 from table where column3 = x;
Another set of queries which benefit from index only scans are those with EXISTS or JOINs just used for filtering the result set. In PostgreSQL 9.1 they always needed to read part of the actual row to check the visibility information. In 9.2 it will for VACUUMED pages only need to check the index and the visibility map.
I, for one, am not excited by the JSON addition at all. On the other hand, index-only scanning, scalability improvement, and reduced power consumption are all very big deal.
CREATE TABLE foo (a_doc xml);
CREATE INDEX doc_title ON foo ((xpath('/root@title', a_doc));
anyway as a possible workaround just see andrew dustan's post:
It seems to be that Postgres is solving the problem of having different databases for your app. I would love to see the Redis datatypes built into it and Postgres might then become the strongest choice when selecting tools for my next app.
Have you considered writing an extension to do that? It may be easier than you think; postgresql has a powerful extension mechanism (you can even do specialized indexing!).
As far as I can tell, JSON only made it into core because there are expected to be many users very quickly. But that doesn't scale -- it's better for users to look at pgxn.org and developers to use the extension mechanism. Once that becomes a matter of course, we'll see an explosion of specialized extensions.
Have you seen?: https://github.com/dpage/redis_fdw