Hello list,
My problem is that I would like to use the gist index with box datatype
for dual-temporal valid_from values. Querying dual-dimensional tables
using boxes is much easier to write, and hopefully the query will also
run more efficiently, than when using the standard approach of direct
comparisons.
Additional reason for using the gist index is that I would like to use
the upcoming exclusion constraint feature, although I haven't installed
9.0 alpha yet.
The table definitions are of the usual dual temporal type:
create table foo (
data ...
db_valid_from timestamp,
db_valid_until timestamp,
real_valid_from timestamp,
real_valid_until timestamp,
check (db_valid_from < db_valid_until),
check (real_valid_from < real_valid_until)
);
The intervals _valid_from - _valid_until should be half-open, that is
[_valid_from, _valid_until).
A helper function to create a time-dimensional box:
create function time_box(db_valid_from timestamp,
db_valid_until timestamp,
real_valid_from timestamp,
real_valid_until timestamp) returns box as
$$
select box(
point(extract(epoch from $1),
extract(epoch from $3)),
point(extract(epoch from $2) - 0.000001,
extract(epoch from $4) - 0.000001)
);
$$
language 'sql';
Here I am trying to simulate the half-open intervals with the -0.000001.
And then I would like to create an index:
create index foo_time_box_idx on foo using gist (
time_box(db_valid_from, db_valid_until,
real_valid_from, real_valid_until) box_ops
);
Unfortunately this doesn't work too well, as extract will give double
precision back, and the precision it can hold will change depending how
far the timestamp is from 2000-01-01. This will cause the half-open
property to disappear when far away from 2000-01-01.
I have tried casting the timestamp as bigint, but this gives even worse
results. Point is defined to be of type (double precision, double
precision), and thus the point constructed from the timestamp -> bigint
will have the same problem (magnified).
So, the question is: Is there any (relatively easy) way to use gist
index with dual-temporal tables? I think this will be asked a lot
when 9.0 with exclusion constraints is released. The feature seems
really powerful, but unfortunately supporting data types seem to be
missing, at least from 8.4.
--
Anssi Kääriäinen.
