Re: Exclude constraint problem - Mailing list pgsql-bugs

Jeff Davis wrote:

> Are you using any floating point values, or floating-point timestamps
> (that's a compile-time option)?

I'm using whatever the default compile time options are.

It's worth noting that if I delete the row, and then attempt to insert
a row with the same data on the original database... it fails.

In this case the period is the 'reported_at' column +- one minute, and
the second, conflicting, row has a 'reported_at' value of more than one
second greater than the original row.

> If you dump a floating-point value, and then reload it, it may be
> different than the one you started with. That can cause a problem with
> either UNIQUE or EXCLUDE constraints.

The index is on an integer (vehicle), a polygon (created with ST_Expand),
and a period.  When I attempt to delete+reinsert the row, I use the
binary representation of the polygon.

> If you are not using floating point values, please try to make a
> self-contained test case that includes data that can reproduce the
> problem.

I'll try.  Input is serialized, so would simply logging the queries
suffice?

Tom: the machine collecting the data is a FreeBSD 7.2p8/x64 box, the other
is a 32-bit Ubuntu 10.04 virtual machine for doing bad things with mapnik.
I certainly *hope* the BSD guys have their FP math stuff in order.

If I try to manually add the constraint on the table I get:

blockface=# alter table bus_positions add constraint "exclude_time_buffer" EXCLUDE USING gist (vehicle WITH =,
buffer_timeWITH &&, bbox_dup WITH &&); 
NOTICE:  ALTER TABLE / ADD EXCLUDE will create implicit index "exclude_time_buffer" for table "bus_positions"
ERROR:  could not create exclusion constraint "exclude_time_buffer"
DETAIL:  Key (vehicle, buffer_time, bbox_dup)=(54, [2010-08-28 07:08:21-07, 2010-08-28 07:10:21-07),
0103000020E6100000010000000500000076374F75C8995EC07E91D09673E5424076374F75C8995EC09A42E73576E54240E8DEC325C7995EC09A42E73576E54240E8DEC325C7995EC07E91D09673E5424076374F75C8995EC07E91D09673E54240)

conflicts with key (vehicle, buffer_time, bbox_dup)=(54, [2010-08-28 07:09:34-07, 2010-08-28 07:11:34-07),
0103000020E6100000010000000500000016FBCBEEC9995EC09A42E73576E5424016FBCBEEC9995EC0B6F3FDD478E5424088A2409FC8995EC0B6F3FDD478E5424088A2409FC8995EC09A42E73576E5424016FBCBEEC9995EC09A42E73576E54240).

When I go back to the original data, yup, there are two rows with those
data in them.

On both machines:

blockface=# SELECT period('2010-08-28 07:08:21-07', '2010-08-28 07:10:21-07') && period('2010-08-28 07:09:34-07',
'2010-08-2807:11:34-07') AS period_intersect,  

'0103000020E6100000010000000500000076374F75C8995EC07E91D09673E5424076374F75C8995EC09A42E73576E54240E8DEC325C7995EC09A42E73576E54240E8DEC325C7995EC07E91D09673E5424076374F75C8995EC07E91D09673E54240'::geometry
&& 

'0103000020E6100000010000000500000016FBCBEEC9995EC09A42E73576E5424016FBCBEEC9995EC0B6F3FDD478E5424088A2409FC8995EC0B6F3FDD478E5424088A2409FC8995EC09A42E73576E5424016FBCBEEC9995EC09A42E73576E54240'::geometry
ASgeom_intersect; 
  period_intersect | geom_intersect
------------------+----------------
  t                | t
(1 row)

Another thing I stumbled over was that I could not (accidentally) insert an
empty period on a column with an exclude constraint using the && operator.

- alex