Re: Exclude constraint problem

Well, the answer is that Jeff's instinct was right: the dump and reload
isn't reproducing the original data exactly. It's not our fault though,
it's a postgis bug. Observe:

gisttest2=# select ST_expand(setsrid(makepoint(-122.50367,37.74189),4326), 0.00004);
st_expand
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0103000020E61000000100000005000000C32ADEC83CA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0ECF5EE8FF7DE424035D252793BA05EC0ECF5EE8FF7DE424035D252793BA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0D044D8F0F4DE4240
(1 row)

gisttest2=# select ST_expand(setsrid(makepoint(-122.50376,37.74185),4326), 0.00004);
st_expand
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0103000020E6100000010000000500000063EE5A423EA05EC042EC4CA1F3DE424063EE5A423EA05EC05E9D6340F6DE4240D595CFF23CA05EC05E9D6340F6DE4240D595CFF23CA05EC042EC4CA1F3DE424063EE5A423EA05EC042EC4CA1F3DE4240
(1 row)

gisttest2=# select ST_expand(setsrid(makepoint(-122.50367,37.74189),4326), 0.00004) && ST_expand(setsrid(makepoint(-122.50376,37.74185),4326), 0.00004);
?column?
----------
f
(1 row)

gisttest2=# select '0103000020E61000000100000005000000C32ADEC83CA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0ECF5EE8FF7DE424035D252793BA05EC0ECF5EE8FF7DE424035D252793BA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0D044D8F0F4DE4240'::geometry &&
gisttest2-# '0103000020E6100000010000000500000063EE5A423EA05EC042EC4CA1F3DE424063EE5A423EA05EC05E9D6340F6DE4240D595CFF23CA05EC05E9D6340F6DE4240D595CFF23CA05EC042EC4CA1F3DE424063EE5A423EA05EC042EC4CA1F3DE4240'::geometry;
?column?
----------
t
(1 row)

So these two geometry values do not overlap in the original database,
but they do overlap in the clone, apparently because the output
representation of geometry doesn't result in an exact reconstruction
of the value. Somebody better complain over in the postgis lists.

regards, tom lane