Unique constraint violation on serial column

Bill Chandler <billybobc1210@yahoo.com> writes:

Client is getting the following error when attempting
to do an insert on a table:

ERROR: duplicate key violates unique constraint
event_tbl_evt_id_key

What's the exact query (or queries) causing this? Do you have any
triggers or rules that might be affecting the table?

regards, tom lane

Tom,

This is not the EXACT command (don't have that since
this a client site and they did not have logging
turned on) but the insert command would have looked
something like:

INSERT INTO EVENT_TBL VALUES(1039850293991, 'X.Y.Z',
1039110343000, '10.25', 1, 739950991)

For what its worth, the command is being issued via
JDBC app.

If the exact command is important I could have them
turn on logging and I could get that for you.

Bill

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bill Chandler <billybobc1210@yahoo.com> writes:

Client is getting the following error when

attempting

to do an insert on a table:

ERROR: duplicate key violates unique constraint
event_tbl_evt_id_key

What's the exact query (or queries) causing this?
Do you have any
triggers or rules that might be affecting the table?

regards, tom lane

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Mon, 2005-04-11 at 11:03 -0700, Bill Chandler wrote:

ERROR: duplicate key violates unique constraint
event_tbl_evt_id_key

EVENT_TBL
evt_id bigserial, unique
d1 numeric(13)
obj_id numeric(6)
d2 numeric(13)
val varchar(22)
correction numeric(1)
delta numeric(13)

and a bit later , in response to a question,
On Mon, 2005-04-11 at 14:24 -0700, Bill Chandler wrote:

Tom,

This is not the EXACT command (don't have that since
this a client site and they did not have logging
turned on) but the insert command would have looked
something like:

INSERT INTO EVENT_TBL VALUES(1039850293991, 'X.Y.Z',
1039110343000, '10.25', 1, 739950991)

firstly, the types do not seem to match the table
definition.

secondly, you seem to be inserting a literal value into your
serial column.

did you mean to say that the insert was
INSERT INTO EVENT_TBL (d1,...) VALUES (...) ?

what is the current value of the sequence ?
are there any rows there evt_id is higher than that ?

gnari

I'm sorry, was working on little sleep yesterday. You
are right, the table was created with the columns in
the following order:

d1, obj_id, d2, val, correction, delta, evt_id

The insert command looks something like:

INSERT INTO EVENT_TBL VALUES(1039850293991, 145,
1039110343000, '10.25', 1, 739950991)

Bill

--- Ragnar Hafsta��� <gnari@simnet.is> wrote:

On Mon, 2005-04-11 at 11:03 -0700, Bill Chandler
wrote:

ERROR: duplicate key violates unique constraint
event_tbl_evt_id_key

EVENT_TBL
evt_id bigserial, unique
d1 numeric(13)
obj_id numeric(6)
d2 numeric(13)
val varchar(22)
correction numeric(1)
delta numeric(13)

and a bit later , in response to a question,
On Mon, 2005-04-11 at 14:24 -0700, Bill Chandler
wrote:

Tom,

This is not the EXACT command (don't have that

since

this a client site and they did not have logging
turned on) but the insert command would have

looked

something like:

INSERT INTO EVENT_TBL VALUES(1039850293991,

'X.Y.Z',

1039110343000, '10.25', 1, 739950991)

firstly, the types do not seem to match the table
definition.

secondly, you seem to be inserting a literal value
into your
serial column.

did you mean to say that the insert was
INSERT INTO EVENT_TBL (d1,...) VALUES (...) ?

what is the current value of the sequence ?
are there any rows there evt_id is higher than that
?

gnari

__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

On Tue, Apr 12, 2005 at 07:59:55AM -0700, Bill Chandler wrote:

I'm sorry, was working on little sleep yesterday. You
are right, the table was created with the columns in
the following order:

d1, obj_id, d2, val, correction, delta, evt_id

The insert command looks something like:

INSERT INTO EVENT_TBL VALUES(1039850293991, 145,
1039110343000, '10.25', 1, 739950991)

So you are indeed inserting constant values in your serial field. (Bad
idea.) Why are you doing that (i.e. why aren't you using nextval()),
and do you have numbers bigger than the sequence's current value already
in the table?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Uno combate cuando es necesario... �no cuando est� de humor!
El humor es para el ganado, o para hacer el amor, o para tocar el
baliset. No para combatir." (Gurney Halleck)

I was able to get a pg_dump of the table in question.
It has 23040 rows in it. evt_id column ranges from 1
to 23040.

I used the dump to create a new database. All inserts
fail with same error (unique constraint violation).
However, I am wondering if this is just the result of
the fact that the pg_dump output conatins explicit
evt_id values for each row added so after creating the
database from the pg_dump output the sequence is still
at 1 when it should really be at 23040.

How does one find out the current sequence value? Is
there a way to change it?

Bill

--- Bill Chandler <billybobc1210@yahoo.com> wrote:

I'm sorry, was working on little sleep yesterday.
You
are right, the table was created with the columns in
the following order:

d1, obj_id, d2, val, correction, delta, evt_id

The insert command looks something like:

INSERT INTO EVENT_TBL VALUES(1039850293991, 145,
1039110343000, '10.25', 1, 739950991)

Bill

--- Ragnar Hafsta��� <gnari@simnet.is> wrote:

On Mon, 2005-04-11 at 11:03 -0700, Bill Chandler
wrote:

ERROR: duplicate key violates unique constraint
event_tbl_evt_id_key

EVENT_TBL
evt_id bigserial, unique
d1 numeric(13)
obj_id numeric(6)
d2 numeric(13)
val varchar(22)
correction numeric(1)
delta numeric(13)

and a bit later , in response to a question,
On Mon, 2005-04-11 at 14:24 -0700, Bill Chandler
wrote:

Tom,

This is not the EXACT command (don't have that

since

this a client site and they did not have logging
turned on) but the insert command would have

looked

something like:

INSERT INTO EVENT_TBL VALUES(1039850293991,

'X.Y.Z',

1039110343000, '10.25', 1, 739950991)

firstly, the types do not seem to match the table
definition.

secondly, you seem to be inserting a literal value
into your
serial column.

did you mean to say that the insert was
INSERT INTO EVENT_TBL (d1,...) VALUES (...) ?

what is the current value of the sequence ?
are there any rows there evt_id is higher than

that

?

gnari

__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Tue, Apr 12, 2005 at 08:48:15AM -0700, Bill Chandler wrote:

How does one find out the current sequence value? Is
there a way to change it?

Using the function setval() you can change it. SELECT * from
sequencename to find out.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Si quieres ser creativo, aprende el arte de perder el tiempo"

I did not intend to put explicit values in the
'evt_id' column. I thought the six values in the
insert command correspond to the 1st six columns in
the create table command, namely d1, obj_id, d2, val,
correction and delta and 'evt_id' is set to the
nextval() automagically. Is that not correct?

Bill

--- Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:

On Tue, Apr 12, 2005 at 07:59:55AM -0700, Bill
Chandler wrote:

I'm sorry, was working on little sleep yesterday.

You

are right, the table was created with the columns

in

the following order:

d1, obj_id, d2, val, correction, delta, evt_id

The insert command looks something like:

INSERT INTO EVENT_TBL VALUES(1039850293991, 145,
1039110343000, '10.25', 1, 739950991)

So you are indeed inserting constant values in your
serial field. (Bad
idea.) Why are you doing that (i.e. why aren't you
using nextval()),
and do you have numbers bigger than the sequence's
current value already
in the table?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Uno combate cuando es necesario... ���no cuando est���
de humor!
El humor es para el ganado, o para hacer el amor, o
para tocar el
baliset. No para combatir." (Gurney Halleck)

__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

On Tue, Apr 12, 2005 at 08:58:41AM -0700, Bill Chandler wrote:

I did not intend to put explicit values in the
'evt_id' column. I thought the six values in the
insert command correspond to the 1st six columns in
the create table command, namely d1, obj_id, d2, val,
correction and delta and 'evt_id' is set to the
nextval() automagically. Is that not correct?

Sorry, I didn't count the fields. My mistake.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Tulio: oh, para qu� servir� este boton, Juan Carlos?
Policarpo: No, al�jense, no toquen la consola!
Juan Carlos: Lo apretar� una y otra vez.

All,

In the pg_dump output is the command:

COPY event_tbl (d1, ..., evt_id) FROM stdin;

followed by all the data for the table. There are
23040 rows. The last value for evt_id is 23040. So
far so good. Then the last statement in the pg_dump
output is:

SELECT pg_catalog.setval('event_tbl_evt_id_seq',
21232, true);

I'm guessing this is my culprit. But this dump is
from the client site. Short of somebody mucking with
the sequence manually, is there any way that the
sequence number could get changed?

As I've said, our insert commands are very simple and
we do not specify 'evt_id' values directly. Does
anybody have any ideas about how this could have
gotten out of sync?

thanks,

Bill

--- Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:

On Tue, Apr 12, 2005 at 08:48:15AM -0700, Bill
Chandler wrote:

How does one find out the current sequence value?

Is

there a way to change it?

Using the function setval() you can change it.
SELECT * from
sequencename to find out.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Si quieres ser creativo, aprende el arte de perder
el tiempo"

__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/