Opened 14 years ago

Closed 6 years ago

#541 closed defect (fixed)

unique constraint on geography not enforced on update

Reported by: arno Owned by: pramsey
Priority: medium Milestone: PostGIS Fund Me
Component: postgis Version: master
Keywords: Cc:

Description

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.

Attachments (1)

test.sql (666 bytes ) - added by arno 14 years ago.
testcase

Download all attachments as: .zip

Change History (7)

by arno, 14 years ago

Attachment: test.sql added

testcase

comment:1 by arno, 14 years ago

Version: 1.5.Xtrunk

comment:2 by robe, 13 years ago

Milestone: PostGIS 1.5.3PostGIS 2.0.0

comment:3 by pramsey, 13 years ago

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.

comment:4 by robe, 13 years ago

Milestone: PostGIS 2.0.0PostGIS Future

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.

comment:5 by robe, 7 years ago

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

comment:6 by pramsey, 6 years ago

Resolution: fixed
Status: newclosed

Should be addressed w/ new btree behavior in 2.4

Note: See TracTickets for help on using tickets.