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_time WITH &&, 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-28 07:11:34-07') AS period_intersect,
'0103000020E6100000010000000500000076374F75C8995EC07E91D09673E5424076374F75C8995EC09A42E73576E54240E8DEC325C7995EC09A42E73576E54240E8DEC325C7995EC07E91D09673E5424076374F75C8995EC07E91D09673E54240'::geometry &&
'0103000020E6100000010000000500000016FBCBEEC9995EC09A42E73576E5424016FBCBEEC9995EC0B6F3FDD478E5424088A2409FC8995EC0B6F3FDD478E5424088A2409FC8995EC09A42E73576E5424016FBCBEEC9995EC09A42E73576E54240'::geometry AS geom_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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
