unique constraint

Started by Dominique Bessette - Halsemaover 18 years ago2 messagesgeneral
Jump to latest

I'm getting the following error, and I think it's because when i insert into
postgres from geoserver's WFS, i first delete then insert the track, and
geoserver does this in succession but very fast. If I personally test
inserting two tracks with the same guid in a row, then it works, but because
geoserver is going so fast it blows up and gives this error. i'm looking
into fixing it another way but does any one have an idea of anything i can
do to avoid this error in postgres?

STATEMENT: INSERT INTO "public"."tracks"
("guid","classification","creationtimestamp","lastupdatetime","effectivetime","reportedtime","staletime","confidencevalue","behavioridentity","nationality","classificationcategory","classconfidencevalue","axislabels","uomlabels","aoumajaxis","aouminaxis","aouvalid","the_geom")
VALUES ('OOSTZEE','U','2008-01-22T21:05:15.366Z','2008-01-22T21:05:15.366Z
','2008-01-22T21:05:15.366Z','2008-01-22T21:05:15.366Z','2008-01-22T21:05:
15.366Z',1.0,'FRIEND','','SEA','1.0','Lat Long h','degree
degree',9999999,9999999,'true',setSRID('00000000014029D8E757928E0D404B9C28CBD1244A'::geometry,4326))

ERROR: duplicate key violates unique constraint "tracks_guid_key"

Track=# \d tracks
Table "public.tracks"
Column | Type | Modifiers
------------------------+-------------------+------------------------------------------------------
sid | integer | not null default
nextval('tracks_sid_seq'::regclass)
guid | character varying | not null
classification | character varying |
creationtimestamp | character varying |
lastupdatetime | character varying |
effectivetime | character varying |
reportedtime | character varying |
staletime | character varying |
confidencevalue | numeric |
behavioridentity | character varying |
nationality | character varying |
classificationcategory | character varying |
classconfidencevalue | character varying |
axislabels | character varying |
uomlabels | character varying |
aoumajaxis | numeric |
aouminaxis | numeric |
aouvalid | character varying |
the_geom | geometry |
Indexes:
"tracks_pkey" PRIMARY KEY, btree (sid)
"tracks_guid_key" UNIQUE, btree (guid)
"tracks_the_geom_gist" gist (the_geom)
Check constraints:
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text
OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)

#2Erik Jones
erik@myemma.com
In reply to: Dominique Bessette - Halsema (#1)
Re: unique constraint

On Jan 24, 2008, at 12:36 PM, Dominique Bessette - Halsema wrote:

I'm getting the following error, and I think it's because when i
insert into postgres from geoserver's WFS, i first delete then
insert the track, and geoserver does this in succession but very
fast. If I personally test inserting two tracks with the same guid
in a row, then it works, but because geoserver is going so fast it
blows up and gives this error. i'm looking into fixing it another
way but does any one have an idea of anything i can do to avoid
this error in postgres?

STATEMENT: INSERT INTO
"public"."tracks" ("guid","classification","creationtimestamp","lastup
datetime","effectivetime","reportedtime","staletime","confidencevalue"
,"behavioridentity","nationality","classificationcategory","classconfi
dencevalue","axislabels","uomlabels","aoumajaxis","aouminaxis","aouval
id","the_geom") VALUES ('OOSTZEE','U','2008-01-22T21:05:
15.366Z','2008-01-22T21:05:15.366Z','2008-01-22T21:05:15.366Z','2008-0
1-22T21:05:15.366Z','2008-01-22T21:05:15.366Z',
1.0,'FRIEND','','SEA','1.0','Lat Long h','degree degree',
9999999,9999999,'true',setSRID
('00000000014029D8E757928E0D404B9C28CBD1244A'::geometry,4326))

ERROR: duplicate key violates unique constraint "tracks_guid_key"

Perhaps you haven't wrapped these successive DELETE -> INSERT
statements in a transaction?

BEGIN;
DELETE ...;
INSERT ...;
COMMIT;

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com