pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Very worried about this
7+ messages / 5 participants
[nested] [flat]

* Very worried about this
@ 2005-03-24 21:21 Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:37 ` Re: Very worried about this Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Kris Kiger @ 2005-03-24 21:21 UTC (permalink / raw)
  To: pgsql-admin

I have a table that looks like this:

                                              Table "public.hd"
      Column       |            Type             
|                                Modifiers
-------------------+-----------------------------+-------------------------------------------------------------------------
 d_id        | integer                     | not null default 
nextval('public.hd_d_id_seq'::text)
 h_id        | integer                     | not null
 src            | text                        |
 p_q | integer                     | not null default 1
 c_id        | integer                     | not null
 insert_time       | timestamp without time zone | default now()
Indexes:
    "hd_pkey" PRIMARY KEY, btree (d_id)
    "hd_idx" btree (h_id, c_id)


_Log output:_

[2005-03-24 15:33:25 EST - 13882 - dbname - 4052774] LOG:  statement: 
INSERT INTO hd (c_id, h_id, p_q) VALUES ((SELECT c_id FROM c WHERE e_id 
= 'tester1'), (SELECT h_id FROM h JOIN c USING (c_id) WHERE e_id = 
'tester1' AND h.active AND NOT p_c),10);

[2005-03-24 15:33:25 EST - 13882 - dbname - 4052774] ERROR:  duplicate 
key violates unique constraint "hd_pkey"


These inserts are being executed ever 1.5 seconds on this database.  
This error does not happen often and appears to happen randomly.  No 
other inserts are being executed on this table except this one.  From 
our current tests 99% of the inserts go through, with the exception of 
these few.  Honestly, I don't see how this could be caused from 
something on my end.  The primary key value is being determined by a 
default, so everything should be handled within the database.  I'm using 
postgres 8.0.1.  Any ideas?

Thanks for the assist!

Kris



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

* Re: Very worried about this
  2005-03-24 21:21 Very worried about this Kris Kiger <kris@musicrebellion.com>
@ 2005-03-24 21:37 ` Scott Marlowe <smarlowe@g2switchworks.com>
  2005-03-24 21:45   ` Re: Very worried about this Kris Kiger <kris@musicrebellion.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Scott Marlowe @ 2005-03-24 21:37 UTC (permalink / raw)
  To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin

On Thu, 2005-03-24 at 15:21, Kris Kiger wrote:
> I have a table that looks like this:
> 
>                                               Table "public.hd"
>       Column       |            Type             
> |                                Modifiers
> -------------------+-----------------------------+-------------------------------------------------------------------------
>  d_id        | integer                     | not null default 
> nextval('public.hd_d_id_seq'::text)
>  h_id        | integer                     | not null
>  src            | text                        |
>  p_q | integer                     | not null default 1
>  c_id        | integer                     | not null
>  insert_time       | timestamp without time zone | default now()
> Indexes:
>     "hd_pkey" PRIMARY KEY, btree (d_id)
>     "hd_idx" btree (h_id, c_id)
> 
> 
> _Log output:_
> 
> [2005-03-24 15:33:25 EST - 13882 - dbname - 4052774] LOG:  statement: 
> INSERT INTO hd (c_id, h_id, p_q) VALUES ((SELECT c_id FROM c WHERE e_id 
> = 'tester1'), (SELECT h_id FROM h JOIN c USING (c_id) WHERE e_id = 
> 'tester1' AND h.active AND NOT p_c),10);
> 
> [2005-03-24 15:33:25 EST - 13882 - dbname - 4052774] ERROR:  duplicate 
> key violates unique constraint "hd_pkey"
> 
> 
> These inserts are being executed ever 1.5 seconds on this database.  
> This error does not happen often and appears to happen randomly.  No 
> other inserts are being executed on this table except this one.  From 
> our current tests 99% of the inserts go through, with the exception of 
> these few.  Honestly, I don't see how this could be caused from 
> something on my end.  The primary key value is being determined by a 
> default, so everything should be handled within the database.  I'm using 
> postgres 8.0.1.  Any ideas?


Are you sure someone hasn't been messing with your sequence some how? 
That's the only way I've seen these kinds of things happen before.



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

* Re: Very worried about this
  2005-03-24 21:21 Very worried about this Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:37 ` Re: Very worried about this Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-03-24 21:45   ` Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:52     ` Re: Very worried about this Michael Fuhr <mike@fuhr.org>
  2005-03-24 21:57     ` Re: Very worried about this Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-24 22:00     ` Re: Very worried about this Steve Crawford <scrawford@pinpointresearch.com>
  0 siblings, 3 replies; 7+ messages in thread

From: Kris Kiger @ 2005-03-24 21:45 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-admin

I'm positive no one else has been in the database.  There are two of us 
who work on it and we have been side by side all afternoon.  The problem 
appears to be purely internal to the database.  I'm running more tests 
as we speak and we are still recieving the same sporadic errors.  It 
works for a long amount of time and then the error occurs, but then 
things work fine again for a while.  The inconsistency is quite troubling.

Thanks

Kris


Scott Marlowe wrote:

>Are you sure someone hasn't been messing with your sequence some how? 
>That's the only way I've seen these kinds of things happen before.
>  
>




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

* Re: Very worried about this
  2005-03-24 21:21 Very worried about this Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:37 ` Re: Very worried about this Scott Marlowe <smarlowe@g2switchworks.com>
  2005-03-24 21:45   ` Re: Very worried about this Kris Kiger <kris@musicrebellion.com>
@ 2005-03-24 21:52     ` Michael Fuhr <mike@fuhr.org>
  2005-03-24 22:01       ` Re: Very worried about this Kris Kiger <kris@musicrebellion.com>
  2 siblings, 1 reply; 7+ messages in thread

From: Michael Fuhr @ 2005-03-24 21:52 UTC (permalink / raw)
  To: Kris Kiger <kris@musicrebellion.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-admin

On Thu, Mar 24, 2005 at 04:45:02PM -0500, Kris Kiger wrote:

> I'm positive no one else has been in the database.  There are two of us 
> who work on it and we have been side by side all afternoon.  The problem 
> appears to be purely internal to the database.  I'm running more tests 
> as we speak and we are still recieving the same sporadic errors.  It 
> works for a long amount of time and then the error occurs, but then 
> things work fine again for a while.  The inconsistency is quite troubling.

What are the results of the following queries?

SELECT max(d_id) FROM hd;
SELECT * FROM hd_d_id_seq;

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



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

* Re: Very worried about this
  2005-03-24 21:21 Very worried about this Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:37 ` Re: Very worried about this Scott Marlowe <smarlowe@g2switchworks.com>
  2005-03-24 21:45   ` Re: Very worried about this Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:52     ` Re: Very worried about this Michael Fuhr <mike@fuhr.org>
@ 2005-03-24 22:01       ` Kris Kiger <kris@musicrebellion.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Kris Kiger @ 2005-03-24 22:01 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-admin

Heh, I see.  The sequence currval was set lower than what was already in 
the table.  It just so happened we had holes in our data for the values 
it was selecting.  Thanks for pointing that out!

Kris

Michael Fuhr wrote:

>On Thu, Mar 24, 2005 at 04:45:02PM -0500, Kris Kiger wrote:
>
>  
>
>>I'm positive no one else has been in the database.  There are two of us 
>>who work on it and we have been side by side all afternoon.  The problem 
>>appears to be purely internal to the database.  I'm running more tests 
>>as we speak and we are still recieving the same sporadic errors.  It 
>>works for a long amount of time and then the error occurs, but then 
>>things work fine again for a while.  The inconsistency is quite troubling.
>>    
>>
>
>What are the results of the following queries?
>
>SELECT max(d_id) FROM hd;
>SELECT * FROM hd_d_id_seq;
>
>  
>




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

* Re: Very worried about this
  2005-03-24 21:21 Very worried about this Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:37 ` Re: Very worried about this Scott Marlowe <smarlowe@g2switchworks.com>
  2005-03-24 21:45   ` Re: Very worried about this Kris Kiger <kris@musicrebellion.com>
@ 2005-03-24 21:57     ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2005-03-24 21:57 UTC (permalink / raw)
  To: Kris Kiger <kris@musicrebellion.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-admin

Kris Kiger <kris@musicrebellion.com> writes:
> I'm positive no one else has been in the database.  There are two of us 
> who work on it and we have been side by side all afternoon.  The problem 
> appears to be purely internal to the database.  I'm running more tests 
> as we speak and we are still recieving the same sporadic errors.

Well, it would be good to positively refute Scott's theory.  Let's see
	select max(d_id) from hd;
and
	select * from hd_d_id_seq;

			regards, tom lane



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

* Re: Very worried about this
  2005-03-24 21:21 Very worried about this Kris Kiger <kris@musicrebellion.com>
  2005-03-24 21:37 ` Re: Very worried about this Scott Marlowe <smarlowe@g2switchworks.com>
  2005-03-24 21:45   ` Re: Very worried about this Kris Kiger <kris@musicrebellion.com>
@ 2005-03-24 22:00     ` Steve Crawford <scrawford@pinpointresearch.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Steve Crawford @ 2005-03-24 22:00 UTC (permalink / raw)
  To: Kris Kiger <kris@musicrebellion.com>; Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-admin

On Thursday 24 March 2005 1:45 pm, Kris Kiger wrote:
> I'm positive no one else has been in the database.  There are two
> of us who work on it and we have been side by side all afternoon. 
> The problem appears to be purely internal to the database.  I'm
> running more tests as we speak and we are still recieving the same
> sporadic errors.  It works for a long amount of time and then the
> error occurs, but then things work fine again for a while.  The
> inconsistency is quite troubling.

Try turning up logging to catch as much as possible (all statements, 
connections and everything). Don't know if it will yield a clue but 
it's a place to start. At least it might trap any statement, however 
generated, that is messing with the sequence if that turns out to be 
the culprit or alternately it might eliminate that possibility.

Cheers,
Steve




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


end of thread, other threads:[~2005-03-24 22:01 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-24 21:21 Very worried about this Kris Kiger <kris@musicrebellion.com>
2005-03-24 21:37 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-24 21:45   ` Kris Kiger <kris@musicrebellion.com>
2005-03-24 21:52     ` Michael Fuhr <mike@fuhr.org>
2005-03-24 22:01       ` Kris Kiger <kris@musicrebellion.com>
2005-03-24 21:57     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 22:00     ` Steve Crawford <scrawford@pinpointresearch.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