Re: duplicate key violates unique constraint

Greg,

Since Postgres allows you to insert a value into a field that is
designated as a sequence number, is it possible that one was entered
that the sequence tried to recreate?

And this is more than a question than an answer, but I thought that
sequences had to designated as data type "serial."

Carol Walter

On May 10, 2007, at 9:38 AM, Spiegelberg, Greg wrote:

> List,
>
> We're using 8.2.1 in RedHat ES 4 here and I have a simple two
> column table: data_keys1.
>
> Table "public.data_keys1"
> Column | Type | Modifiers
> --------+--------
> +---------------------------------------------------------
> id | bigint | not null default nextval
> ('data_keys1_id_seq'::regclass)
> key1 | text |
> Indexes:
> "data_keys1_pkey" PRIMARY KEY, btree (id)
> I also have a C program using libpq that populates this table via a
> PREPARE'd statement within a transaction. Recently, while this
> table was being loaded, it encountered an error I hadn't seen
> before and is a bit confusing.
>
> 2007-05-09 09:27:07 EDT [22853] : LOG: statement: EXECUTE
> insertKey1('vgdisplay');
> 2007-05-09 09:27:07 EDT [22853] : DETAIL: prepare: PREPARE
> insertKey1(text) AS INSERT INTO public.data_keys1 (key1) values ($1);
> 2007-05-09 09:27:07 EDT [22853] : ERROR: duplicate key violates
> unique constraint "data_keys1_pkey"
> 2007-05-09 09:27:07 EDT [22853] : STATEMENT: EXECUTE insertKey1
> ('vgdisplay');
>
> As you can see, it's only providing the key1 column and the
> sequence is providing the value for the column with the
> constraint. How can this be happening? There were no other
> transactions, commits, individual inserts happenning at the same
> time or within several seconds of this one.
>
> I'd like to explore sequences a bit more b/c as they are
> implemented in PostgreSQL is a little confusing. Normally, I'd
> start another thread but it may have some bearing here.
>
>
> Sequences...
>
> Now, I don't know if this just hasn't been tested or is a
> documented feature (a.k.a. bug) but something does not seem right
> here. In this test case I'm able to get the same sequence ID's via
> two psql connections to the same database on the same sequence.
>
> Connect to "db" in two different psql sessions (I'll prefix them
> below with 1: and 2:) and in one create the table
>
> 1: db=# CREATE TABLE t1 ( s serial, i int);
> 1: db=# \d t1
> Table "public.t1"
> Column | Type | Modifiers
> --------+---------+------------------------------------------------
> s | integer | not null default nextval('t1_s_seq'::regclass)
> i | integer |
>
> 1: db=# SELECT * FROM t1_s_seq;
> sequence_name | last_value | increment_by | max_value |
> min_value | cache_value | log_cnt | is_cycled | is_called
> ---------------+------------+--------------+---------------------
> +-----------+-------------+---------+-----------+-----------
> t1_s_seq | 12 | 1 | 9223372036854775807
> | 1 | 1 | 32 | f | t
> (1 row)
>
> So, cache on t1_s_seq is set to 1. Not sure why the max_value is
> so high when the column was specified as a 'serial' not a 'serial8'
> but perhaps I should check the code out and submit a patch for
> that. I digress.
>
> Check out the value for t1_s_seq on connection #1.
>
> 1: db=# select nextval('t1_s_seq');
> nextval
> ---------
> 1
> (1 row)
>
> And check the value for t1_s_seq on connection #2.
>
> 2: db=# select nextval('t1_s_seq');
> nextval
> ---------
> 2
> (1 row)
>
> So far, so good. Now start a transaction on connection #1, advance
> t1_s_seq by 10 but don't commit;
>
> 1: db=# begin;
> BEGIN
> 1: db=# select setval('t1_s_seq', currval('t1_s_seq')+10);
> setval
> --------
> 11
> (1 row)
>
> And check the current value for t1_s_seq on connection #2.
>
> 2: db=# select currval('t1_s_seq');
> nextval
> ---------
> 2
> (1 row)
>
>
> That's expected since the transaction on connection #1 hasn't been
> commited. Now commit the transaction on connection #1 and check
> it's current value.
>
> 1: db=# commit;
> COMMIT
> 1: db=# select currval('t1_s_seq');
> currval
> ---------
> 11
> (1 row)
>
> Again, expected. Now let's check the current value on connection
> #2 again.
>
> 2: db=# select currval('t1_s_seq');
> currval
> ---------
> 2
> (1 row)
>
> This is where I take issue with the output. I'm not sure what
> benefit cache value has as the client should, in this case,
> consulted with the backend as too the value of the sequence.
>
> Sequences are suppose to be unique but in this case it seems that
> may not always be the case. Sequences have some kind of odd
> relationship (no pun intended) with transactions in that they are
> in some cases in sync regardless of the connection or query and in
> other situations, such as above, are out of sync.
>
> Just for fun, select on connection #2 the nextval of the sequence.
>
> 2: db=# select nextval('t1_s_seq');
> nextval
> ---------
> 12
> (1 row)
>
> *boogle*
>
> What have I done wrong here? Does it have any bearing on my unique
> constraint error? I wouldn't think so but I haven't found any
> other possible explanation.
>
> TIA,
> Greg
>
> --
> Greg Spiegelberg
> Manager, Product Development
> ISOdx Solutions, a division of Cranel, Inc.
> gspiegelberg(at)isodxsolutions(dot)com
> 614.318.4314, office
> 614.431.8388, fax
>
>
>