From: | Alex Zepeda <zipzippy(at)sonic(dot)net> |
---|---|
To: | Jeff Davis <pgsql(at)j-davis(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Exclude constraint problem |
Date: | 2010-08-30 23:58:16 |
Message-ID: | 4C7C4598.3050002@sonic.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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_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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2010-08-31 00:00:53 | Re: Exclude constraint problem |
Previous Message | Tom Lane | 2010-08-30 23:40:14 | Re: Exclude constraint problem |