unique constraint on geography not enforced on update

[arno]

Hi, when creating a column of type geography, and declaring it as unique, I can't insert two rows with similar geography (that's normal). But I can insert a point with a different geography, and update it afterwards. I'll insert a sql example to be more clear.

The bug does not appear when using geometry instead of geography.

[arno]

testcase

[pramsey]

I get even less consistent results. Using geometry under 2.0, getting this:

postgis20=# CREATE TABLE test (
postgis20(#     id SERIAL PRIMARY KEY,
postgis20(#     geom geometry UNIQUE NOT NULL
postgis20(# );
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_geom_key" for table "test"
CREATE TABLE
postgis20=# INSERT INTO test (geom) VALUES ('POINT(1 3)');
INSERT 0 1
postgis20=# INSERT INTO test (geom) VALUES ('POINT(1 2)');
ERROR:  duplicate key value violates unique constraint "test_geom_key"
DETAIL:  Key (geom)=(0101000000000000000000F03F0000000000000040) already exists.
postgis20=# 

The unique constraint is built using a btree index on geometry which means the btree eq and cmp operators are where the action is. This is similar to the "can we hash a geometry" problem again. A consistent, comparable hash is what we're looking for here.

[robe]

Paul -- feel free to move this back if you want. Just want to gt rid of the clutter in our PostGIS 2.0.0 milestone so it includes just things we really plan to get done in this release. This sounds like not a simple issue to fix and to me I don't see much benefit in putting a unique constraint on a geometry. Why don't you just use exclusion constaints which is what they are designed for and which is designed to use GIST indexes.

​http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/

Granted I think we have issues with that too, but I'd rather deal with those issues than btree ones.

[robe]

Milestone renamed

[pramsey]

Should be addressed w/ new btree behavior in 2.4