Thread: pgadmin or pgsql bug? inserting row

I'm fairly confused by this...I have a table called 'contact' with a 
serial id column, so it automatically has this constraint:

ALTER TABLE contact  ADD CONSTRAINT contact_pkey PRIMARY KEY(id);

Now, I tried several times in 1.8.0 to add the row just by typing new 
data in the Edit Table window, and I got the same error every time.  The 
log looked like this (except with the actual name...which was unique 
BTW, not that it should matter in this context):

2007-08-07 15:45:53 127.0.0.1 dev LOG:  statement: INSERT INTO 
contact(name, parent, nickname) VALUES ('[name removed]'::text, 
'1'::integer, '[nickname removed]'::text)
2007-08-07 15:45:53 127.0.0.1 dev ERROR:  duplicate key violates unique 
constraint "contact_pkey"
2007-08-07 15:45:53 127.0.0.1 dev STATEMENT:  INSERT INTO contact(name, 
parent, nickname) VALUES ('[name removed]'::text, '1'::integer, 
'[nickname removed]'::text)

Now, I'm not sure how that's possible, given that I didn't type an ID, 
one wasn't being passed to the database, and in any case it's serial so 
this shouldn't happen, right?

To make things more interesting, I went over to my own software's UI 
that does the same thing, and it worked!  In the log, there was:

2007-08-07 15:47:04 127.0.0.1 dev LOG:  execute dbdpg_33: insert into 
contact (parent,nickname,name) values ($1,$2,$3) returning id
2007-08-07 15:47:04 127.0.0.1 dev DETAIL:  parameters: $1 = '1', $2 = 
'[nickname removed]', $3 = '[name removed]'

One other detail, after it worked I noticed that the ID returned was one 
where there had been a gap in the sequence (i.e., we had entries 1-12 
and 14-24, and the new row had id 13.)  This may be because the existing 
entries had been added with explicit id numbers but the sequence was 
probably still at a lower number.

This is pretty befuddling to me.  I'm not sure if it's a pgsql issue or 
a pgadmin one, but anyway, there's my report.

Kev

Kevin Field wrote:
> One other detail, after it worked I noticed that the ID returned was one
> where there had been a gap in the sequence (i.e., we had entries 1-12
> and 14-24, and the new row had id 13.)  This may be because the existing
> entries had been added with explicit id numbers but the sequence was
> probably still at a lower number.
> 
> This is pretty befuddling to me.  I'm not sure if it's a pgsql issue or
> a pgadmin one, but anyway, there's my report.

Check the current value of the sequence used by the serial column, and
if it's less than the highest value in the table, you're almost
certainly trying to insert a duplicate. Each time you try though, the
sequence increments whether or not the insert worked - so by chance it
was OK in your app when you hit the gap.

The solution would be to set the current value of the sequence to
something higher than any record already in the table.

Regards, Dave

Dave Page wrote:
> Kevin Field wrote:
>> One other detail, after it worked I noticed that the ID returned was one
>> where there had been a gap in the sequence (i.e., we had entries 1-12
>> and 14-24, and the new row had id 13.)  This may be because the existing
>> entries had been added with explicit id numbers but the sequence was
>> probably still at a lower number.
>>
>> This is pretty befuddling to me.  I'm not sure if it's a pgsql issue or
>> a pgadmin one, but anyway, there's my report.
> 
> Check the current value of the sequence used by the serial column, and
> if it's less than the highest value in the table, you're almost
> certainly trying to insert a duplicate. Each time you try though, the
> sequence increments whether or not the insert worked - so by chance it
> was OK in your app when you hit the gap.
> 
> The solution would be to set the current value of the sequence to
> something higher than any record already in the table.
> 
> Regards, Dave
> 

Oh, of course!  Right.  It was so coincidental it threw me for a loop.  :)

Thanks,
Kev