pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Duplicate Key -- Have I missed something
3+ messages / 2 participants
[nested] [flat]

* Duplicate Key -- Have I missed something
@ 2005-08-07 02:00  Robert Perry <rlperry@lodestonetechnologies.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Robert Perry @ 2005-08-07 02:00 UTC (permalink / raw)
  To: pgsql-admin

     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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Duplicate Key -- Have I missed something
@ 2005-08-07 02:47  Michael Fuhr <mike@fuhr.org>
  parent: Robert Perry <rlperry@lodestonetechnologies.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Fuhr @ 2005-08-07 02:47 UTC (permalink / raw)
  To: Robert Perry <rlperry@lodestonetechnologies.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Duplicate Key -- Have I missed something
@ 2005-08-07 03:18  Robert Perry <rlperry@lodestonetechnologies.com>
  parent: Michael Fuhr <mike@fuhr.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Robert Perry @ 2005-08-07 03:18 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-admin

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
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-08-07 03:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-07 02:00 Duplicate Key -- Have I missed something Robert Perry <rlperry@lodestonetechnologies.com>
2005-08-07 02:47 ` Michael Fuhr <mike@fuhr.org>
2005-08-07 03:18   ` Robert Perry <rlperry@lodestonetechnologies.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox