pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDuplicate 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