Re: Problems with sequences

On Sep 7, 2006, at 5:35 AM, Alban Hertroys wrote:

> Arturo Perez wrote:
>> What happens is that if I do a select nextval('seq') I get a number
>> that's lower than the
>> max primary key id.  This is inspite of my doing
>>    SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
>>    ALTER SEQUENCE seq RESTART WITH <max + 1>;
>>    select pg_catalog.setval(seq, <max+1>, true);
>
> This seems to be a bit over the top;
>     SELECT setval('seq', (SELECT MAX(seq_ID) FROM table)
> should be enough. Even the +1 isn't necessary, as the first value
> the sequence will return is already 1 higher than the value
> retrieved from MAX.

Note that all of the above was in an attempt to reset the sequence to
the proper value.  I'm beginning to think that it's a library problem
as this morning I get:

iht=> select max(article_id) from article;
max
------
4992
(1 row)

iht=> select nextval('pk_article');
nextval
---------
     4986
(1 row)

Assuming the sequence number is being used correctly why would they
be 6 apart?

>
> Are you sure you're using the correct sequence(s) to retrieve your
> column values for the problematic table(s)? How do you set the
> values for seqID?

I tried statement logging but I am not sure it reported anything
useful.  When I get into work I'll send in those logs.

-arturo