Thread: Unique constraint violation on serial column
Hello, 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 Client is using PostgreSQL 7.4.2 on Sparcv9 running Solaris. We have the following tables: 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) CONTROL_TBL obj_id numeric(6), unique name varchar(22), unique dtype numeric(2) dfreq numeric(2) Indexes: EVENT_TBL.d1 (non-clustered) EVENT_TBL.obj_id (non-clustered) CONTROL_TBL.obj_id (non-clustered) CONTROL_TBL.name (clustered) Update processes run continually throughout the day in which rows are inserted. Rows are purged by a different process on a daily basis. Rows are added/purged in a queue-like manner, with older rows (i.e. lower 'evt_id' value) purged first. The EVENT_TBL is potentially large (on the order of millions of rows) but certainly not big enough where a 'bigserial' value would ever wrap back to 0. We also drop all the indexes listed above and recreate them on a daily basis. However, we do not do anything to the sequence or index created for the 'evt_id' column. In fact, we do not ever try to do anything directly to 'evt_id' column. Even on inserts we are not specifying a value for 'evt_id' and let the system do its magic. We never attempt to minipulate the sequence/index for the column in any way. Short of the client mucking around with the column in an unauthorized manner, can anybody think of a reason how things might get confused and an already used 'evt_id' value is used again? Regards, Bill __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
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/