Thread: Error in exclusion constraint error message (8.5)?

Hi,
I tried to use exclusion for time ranges, with this table and data:

CREATE TABLE test (
    from_ts TIMESTAMPTZ,
    to_ts   TIMESTAMPTZ,
    CHECK ( from_ts < to_ts ),
    CONSTRAINT overlapping_times EXCLUDE USING GIST (
        box(
            point( extract(epoch FROM from_ts at time zone 'UTC'), extract(epoch FROM from_ts at time zone 'UTC') ),
            point( extract(epoch FROM to_ts at time zone 'UTC')  , extract(epoch FROM to_ts at time zone 'UTC') )
        ) WITH &&
    )
);
INSERT INTO test ( from_ts, to_ts ) VALUES ( '2009-01-01 01:23:45 EST', '2009-01-10 23:45:12 EST' );
INSERT INTO test ( from_ts, to_ts ) VALUES ( '2009-02-01 01:23:45 EST', '2009-02-10 23:45:12 EST' );
INSERT INTO test ( from_ts, to_ts ) VALUES ( '2009-01-08 00:00:00 EST', '2009-01-15 23:59:59 EST' );

3rd insert fails (correctly), but I have doubts about its error message, which was:

psql:z.sql:18: ERROR:  conflicting key value violates exclusion constraint "overlapping_times"
DETAIL:  Key (box(point(date_part('epoch'::text, timezone('UTC'::text, from_ts)), date_part('epoch'::text,
timezone('UTC'::text,from_ts))), point(date_part('epoch'::text, timezone('UTC'::text, to_ts)), date_part('epoch'::text,
timezone('UTC'::text,to_ts)))))=((1232078399,1232078399),(1231387200,1231387200)) conflicts with existing key
(box(point(date_part('epoch'::text,timezone('UTC'::text, from_ts)), date_part('epoch'::text, timezone('UTC'::text,
from_ts))),point(date_part('epoch'::text, timezone('UTC'::text, to_ts)), date_part('epoch'::text, timezone('UTC'::text,
to_ts)))))=((1232078399,1232078399),(1231387200,1231387200)).

Please not that both box values are the same, and relate to row:
( '2009-01-08 00:00:00 EST', '2009-01-15 23:59:59 EST' )
while I would assume that one of the boxes in error message should relate to row:
( '2009-01-01 01:23:45 EST', '2009-01-10 23:45:12 EST' )
I.e. contain box: (1231645512,1231645512),(1230787425,1230787425)

Is it my lack of understanding? If yes, what is the rationale behind providing the same value twice?

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

hubert depesz lubaczewski <depesz@depesz.com> writes:
> 3rd insert fails (correctly), but I have doubts about its error message, which was:

> psql:z.sql:18: ERROR:  conflicting key value violates exclusion constraint "overlapping_times"
> DETAIL:  Key (box(point(date_part('epoch'::text, timezone('UTC'::text, from_ts)), date_part('epoch'::text,
timezone('UTC'::text,from_ts))), point(date_part('epoch'::text, timezone('UTC'::text, to_ts)), date_part('epoch'::text,
timezone('UTC'::text,to_ts)))))=((1232078399,1232078399),(1231387200,1231387200)) conflicts with existing key
(box(point(date_part('epoch'::text,timezone('UTC'::text, from_ts)), date_part('epoch'::text, timezone('UTC'::text,
from_ts))),point(date_part('epoch'::text, timezone('UTC'::text, to_ts)), date_part('epoch'::text, timezone('UTC'::text,
to_ts)))))=((1232078399,1232078399),(1231387200,1231387200)).

Yeah, this is a bug; and it reflects a wrong test not only a wrong error
message.  Patch applied --- thanks for the report!

            regards, tom lane

On Sat, Jan 2, 2010 at 1:37 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> =A0 =A0CONSTRAINT overlapping_times EXCLUDE USING GIST (
> =A0 =A0 =A0 =A0box(
> =A0 =A0 =A0 =A0 =A0 =A0point( extract(epoch FROM from_ts at time zone 'UT=
C'), extract(epoch FROM from_ts at time zone 'UTC') ),
> =A0 =A0 =A0 =A0 =A0 =A0point( extract(epoch FROM to_ts at time zone 'UTC'=
) =A0, extract(epoch FROM to_ts at time zone 'UTC') )
> =A0 =A0 =A0 =A0) WITH &&
>

Hm, this would be easier to write as:

CONSTRAINT overlapping_times EXCLUDE USING GIST (
  tinterval(from_ts::abstime, to_ts::abstime) WITH &&

But that still seems a bit more complex than ideal. Would it be
reasonable to have a tinterval() constructor which takes timestamptz
data types? Then you could just write

CONSTRAINT overlapping_times
  EXCLUDE USING GIST (tinterval(from_ts, to_ts)) USING &&


--=20
greg

Greg Stark <gsstark@mit.edu> writes:
> But that still seems a bit more complex than ideal. Would it be
> reasonable to have a tinterval() constructor which takes timestamptz
> data types?

No, because that would be encouraging people to use tinterval ;-).
That type needs to die.

If Jeff doesn't finish his range-type stuff soon, maybe a tinterval
replacement that hasn't got a Y2038 problem would be in order.  But
we shouldn't put any more effort into tinterval as such.

(BTW, tinterval hasn't got a gist opclass either, so the constructor
is the least of the missing pieces here.)

            regards, tom lane