Re: RFC: Temporal Extensions for PostgreSQL

"Dawid Kuroczko" <qnex42(at)gmail(dot)com> writes:
> ... Now, assuming UNIQUE INDEX on such table, the order would be preserved
> since no two intervals can overlap. And no overlapping data could be inserted
> without breaking "ovelapivity". And of course non-unique index would
> produce garbage (since left of/right of wouldn't make any sense anymore).

I think actually it doesn't work for unique indexes either :-( because
of dead tuples. Consider that we have in the index

...
(1,2)
(6,8) DEAD
(4,10)
(12,14)
...

Since under the given operators (6,8) and (4,10) are "equal", btree will
not guarantee that those index entries appear in any particular relative
order. Thus the above is a legal index configuration. Now insert (3,5).
This should surely be rejected because it overlaps (4,10). But what
may well happen is that it gets compared to (1,2) --- OK, it's greater
--- and to (6,8) --- OK, it's less --- and then the uniqueness check stops,
because if it's less than (6,8) then there is no need to search further.
Ooops.

*This* is why the transitive law is essential.

regards, tom lane