PSQLException: ERROR: duplicate key value

[Matt Raible]

Hello all,

I'm using PostgreSQL as my database and evolutions to create my table. Here's a sample table:

CREATE TABLE Workout (
id SERIAL PRIMARY KEY,
title varchar(255) NOT NULL,
description text NOT NULL,
distance float8 NOT NULL,
duration float8 NOT NULL,
postedAt timestamp NOT NULL,
athleteId bigint REFERENCES Athlete
);

The SERIAL keyword causes postgresql to create an index that's defined as follows:

-- Sequence: workout_id_seq

-- DROP SEQUENCE workout_id_seq;

CREATE SEQUENCE workout_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE workout_id_seq
OWNER TO postgres;

When I start my app, I load a few workouts in a BootStrap.scala class and they have ids 1, 2 and 3, respectively.

However, when I try to add a new workout using the following code:

def postWorkout(id: Option[Long]) = {
val workout = params.get("workout", classOf[Workout])
Validation.valid("workout", workout)

if (Validation.hasErrors) {
renderArgs.put("template", "Profile/edit")
edit(id);
} else {
id match {
case Some(id) => {
Workout.update(workout)
}
case None => {
workout.postedAt = new java.util.Date
workout.athleteId = 1
Workout.create(workout)
flash += "success" -> ("Nice workout!")
}
}
Action(index())
}
}

It fails with the following error:

play.exceptions.JavaExecutionException: ERROR: duplicate key value violates unique constraint "workout_pkey"
Detail: Key (id)=(1) already exists.
at play.mvc.ActionInvoker.invoke(ActionInvoker.java:229)
at Invocation.HTTP Request(Play!)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "workout_pkey"
Detail: Key (id)=(1) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
at play.db.anorm.Sql$class.execute1(Anorm.scala:918)
at play.db.anorm.SimpleSql.execute1(Anorm.scala:836)
at play.db.anorm.M$class.create(Anorm.scala:407)
at play.db.anorm.Convention$Magic.create(Anorm.scala:277)
at controllers.Profile$.postWorkout(Profile.scala:64)

Does anyone know why the sequence isn't reset when BootStrap.scala loads my initial-data.yml?

Thanks,

Matt

[S Barlow]

I too am encountering this error with play2. I have a couple of objects loaded in the initial-data.yml and then when I attempt to create the first object when application has loaded I get this error. Second attempt works - leading to the thought that something gets initialized in EBean maybe? after the first error. I also get this error with h2 in memory database.

[Rui Lopes da Silva]

Hi, I'm having this problem as well. Already tried to put some JPA annotations like:

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY) 

long id; 

AND

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)

long id;

But nothing seems to work.

I also noted that play every time e restart my app starts the id generator by incrementing 20.

First time I start play the first id=0, and then goes id=1,id=2, ...

Next time first id=20, then id=21, ...

but if i add a row and set manually the id and its before the ones play created, I will get this error.

[Rúben Nascimento Paixão]

Hi,

I've the same problem. However I've created a SEQUENCE  and an annotation (@GeneratedValue(strategy= GenerationType.IDENTITY)) to each primary key but it doesn't work properly. Sometimes it works but unfortunately this error  happens:

• Jun 12 14:57:49 secure-sands-3754 app/web.1: play.api.Application$$anon$1: Execution exception[[PersistenceException: ERROR executing DML bindLog[] error[ERROR: duplicate key value violates unique constraint "hypothesis_pkey"\n Detail: Key (id)=(10566) already exists.]]]

U I don't know what to do. Play should know what would be the next ID in the sequence and should also know what ID's are being already used!

[Alexander Loginov]

Hi all!

I'm usign @GeneratedValue(strategy = GenerationType.IDENTITY). Tried AUTO. Created sequences in DB(Posgress), but error still occurs.

May be we should create a ticket?

Thank you!

Alex

среда, 9 ноября 2011 г., 16:41:34 UTC+2 пользователь Matt Raible написал:

[Alexander Loginov]

I've figured out, that play 2.1.1 generates same SQL for all GenerationType strategies:

select nextval('albums_seq'), s.generate_series from (select generate_series from generate_series(1,20) ) as s

And if you've uploaded db dump, and you have "spaces" between IDs, you'll have this problem.

But question still open: how to fix this?

вторник, 18 июня 2013 г., 23:32:42 UTC+3 пользователь Alexander Loginov написал: