Duplicate Key -- Have I missed something

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Duplicate Key -- Have I missed something

robert Perry-4
    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
Reply | Threaded
Open this post in threaded view
|

Re: Duplicate Key -- Have I missed something

Michael Fuhr
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

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

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Duplicate Key -- Have I missed something

robert Perry-4
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
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster