Expected behavior?

[ddebernardy]

backend=# \d test_revs
                                    Table "test.test_revs"
   Column   |           Type           |                       Modifiers                       
------------+--------------------------+-------------------------------------------------------
 id         | integer                  | not null default nextval('test_id_seq'::regclass)
 rev        | integer                  | not null default nextval('test_rev_seq'::regclass)
 status     | activatable              | not null default 'draft'::activatable
 live       | boolean                  | not null default false
 draft      | boolean                  | not null default false
 start_date | timestamp with time zone | not null default (now())::timestamp(0) with time zone
 stop_date  | timestamp with time zone | not null default 'infinity'::timestamp with time zone
 name       | character varying        | not null default ''::character varying
Indexes:
    "test_revs_pkey" PRIMARY KEY, btree (rev)
    "test_id_rev_key" UNIQUE CONSTRAINT, btree (id, rev)
    "test_draft_excl" EXCLUDE USING btree (id WITH =) WHERE (draft) DEFERRABLE INITIALLY DEFERRED
    "test_live_excl" EXCLUDE USING btree (id WITH =) WHERE (live) DEFERRABLE INITIALLY DEFERRED
    "test_period_excl" EXCLUDE USING gist (id WITH =, period(start_date, stop_date) WITH &&) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "test" CONSTRAINT "test_id_draft_fkey" FOREIGN KEY (id, draft) REFERENCES test_revs(id, rev) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    TABLE "test" CONSTRAINT "test_id_rev_fkey" FOREIGN KEY (id, rev) REFERENCES test_revs(id, rev) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    TABLE "test_rel_revs" CONSTRAINT "test_rel_test_rev_fkey" FOREIGN KEY (test_id, test_rev) REFERENCES test_revs(id, rev) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
Triggers:
    _10_test_revs__upd AFTER UPDATE ON test_revs FOR EACH ROW WHEN (old.rev <> new.rev) EXECUTE PROCEDURE test_revs__upd()

backend=# select * from test_revs;
 id | rev | status | live | draft |       start_date       |       stop_date        | name 
----+-----+--------+------+-------+------------------------+------------------------+------
  1 |   1 | draft  | f    | f     | 2011-06-08 17:15:14+02 | 2011-06-08 17:15:14+02 | 
  1 |   2 | draft  | t    | t     | 2011-06-08 17:15:14+02 | 2011-06-08 17:15:14+02 | foo
(2 rows)

backend=# update test_revs set stop_date = stop_date + interval '1 sec';
ERROR:  conflicting key value violates exclusion constraint "test_period_excl"
DETAIL:  Key (id, period(start_date, stop_date))=(1, [2011-06-08 17:15:14+02, 2011-06-08 17:15:15+02)) conflicts with existing key (id, period(start_date, stop_date))=(1, [2011-06-08 17:15:14+02, 2011-06-08 17:15:15+02)).

I get the second one, which generates an error.

The first took me by surprise. (In a good way, too, but I'd like to make sure it's the expected behavior before I rely on it.)

[jeff-davis]

OK, what it looks like to me is that you are creating empty periods. For instance, try:

select period('2009-01-01', '2009-01-01');

An empty period doesn't overlap with anything, so you can have as many of those as you want. When you add a second to only the end value, then it becomes a non-empty period, and therefore can overlap with other periods (and conflict).

Please do some experimentation and confirm that this matches your use case and expectations.

[ddebernardy]

Confirming it's working as expected then. I've been playing with it most of yesterday afternoon.

To my great delight, too, as I was really scratching my head on how to store the non-live rows. :-)