Thread: Duplicate Key -- Have I missed something
I have a production environment that is running the code that caused this problem about once every 30 seconds or so, with two clients. It normally runs very well, but I was suddenly hit with a duplicate key violation for a table where the primary key is generated in part from sequence.
How might this have happen?
ERROR: duplicate key violates unique constraint "game_pay_cash_coupon_trans_pkey"
CONTEXT: SQL statement "INSERT INTO game_pay_cash_coupon_trans( ttype, lid, id, gpid, cash_paid, trans_user ) values( 'INSERT', $1 , $2 , $3 , $4 , $5 )"
PL/pgSQL function "game_pay_cash_coupon_add" line 7 at SQL statement
SQL statement "SELECT game_pay_cash_coupon_add( $1 , gpk, cash_in_use ) FROM game_state_game gsg, game_state_cash_coupon gscc WHERE gsg.gid = $2 AND gscc.gid = $3 "
PL/pgSQL function "game_state_complete_payment" line 13 at perform
SQL statement "SELECT game_state_complete_payment( $1 , $2 )"
PL/pgSQL function "game_state_game_complete" line 20 at perform
The offeded table
Table "public.game_pay_cash_coupon_trans"
Column | Type | Modifiers
------------+-----------------------------------+------------------------------------------------------------------
toid | d_location_id | not null default "location"()
tid | d_trans_id | not null default nextval('game_pay_cash_coupon_trans_seq'::text)
ttype | d_game_pay_cash_coupon_trans_type |
lid | d_location_id |
id | d_game_pay_cash_coupon_id |
gpid | d_game_play_id |
cash_paid | d_money |
trans_user | d_user_name |
trans_time | d_timestamp |
Indexes:
"game_pay_cash_coupon_trans_pkey" PRIMARY KEY, btree (toid, tid)
Triggers:
trig_game_pay_cash_coupon_trans_aft_ins AFTER INSERT ON game_pay_cash_coupon_trans FOR EACH ROW EXECUTE PROCEDURE trig_func_game_pay_cash_coupon_trans_aft_ins()
Has OIDs: no
The sequence in question
mallball=> select * from game_pay_cash_coupon_trans_seq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
--------------------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
game_pay_cash_coupon_trans_seq | 100000815 | 1 | 9223372036854775807 | 1 | 1 | 32 | f | t
The build in question
mallball=> select * from version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3)
Thanks
Robert Perry
On Sat, Aug 06, 2005 at 10:00:10PM -0400, Robert Perry wrote: > I have a production environment that is running the code that > caused this problem about once every 30 seconds or so, with two > clients. It normally runs very well, but I was suddenly hit with a > duplicate key violation for a table where the primary key is > generated in part from sequence. > > How might this have happen? Have any values been inserted that weren't obtained from the sequence? What are the results of the following queries? SELECT max(tid) FROM game_pay_cash_coupon_trans; SELECT * FROM game_pay_cash_coupon_trans_seq; (You already showed the second query, but let's see it again run at the same time as the first). -- Michael Fuhr
Michael Thanks for taking the blinders off me! This table is new to the database and it seems that the script that I ran to populate it with its' initial data was VERY wrong. I know it seems very odd that I would forget a script I ran less than a week ago regarding a brand new table, but it is just one of those panic things I guess. Making a longs story short. Yes, the problem is existing data and not that new data. Thanks Robert Perry On Aug 6, 2005, at 10:47 PM, Michael Fuhr wrote: > On Sat, Aug 06, 2005 at 10:00:10PM -0400, Robert Perry wrote: > >> I have a production environment that is running the code that >> caused this problem about once every 30 seconds or so, with two >> clients. It normally runs very well, but I was suddenly hit with a >> duplicate key violation for a table where the primary key is >> generated in part from sequence. >> >> How might this have happen? >> > > Have any values been inserted that weren't obtained from the sequence? > What are the results of the following queries? > > SELECT max(tid) FROM game_pay_cash_coupon_trans; > SELECT * FROM game_pay_cash_coupon_trans_seq; > > (You already showed the second query, but let's see it again run at > the same time as the first). > > -- > Michael Fuhr >