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 |
