Re: Sequence and nextval problem

At 11:20 PM 11/24/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Message-Id: <0FCB821D-C666-4FB9-B9FE-BA7B9EECE8C5(at)rvt(dot)dds(dot)nl>
>From: ries van Twisk <pg(at)rvt(dot)dds(dot)nl>
>To: Tk421 <vrobador(at)gmail(dot)com>
>In-Reply-To: <492AFC8C(dot)302(at)gmail(dot)com>
>Subject: Re: Sequence and nextval problem
>Date: Mon, 24 Nov 2008 16:21:40 -0500
>References: <492AFC8C(dot)302(at)gmail(dot)com>
>X-Archive-Number: 200811/144
>X-Sequence-Number: 31928
>
>On Nov 24, 2008, at 2:12 PM, Tk421 wrote:
>> The conversion from access database to postgres worked fine.
>>Everithing it's ok. But now, when i use my database i've found a
>>problem with sequences. In the conversion, the "autonumeric" fields
>>from access have been converted to sequences, everithing ok in a
>>first view. The problem comes because the autonumeric fields in
>>access always return the last value of the table +1, but postgres
>>no. Postgres returns "lost" (i don't know how to call them) values.
>>An example.
>>
>>[snip]
>> In access if i execute "INSERT INTO table (description) VALUES
>>('desc 8'), the result row is 8 | desc 8
>> But in postgres the same query te result row is 3 | desc 8
>>
>> My question is, can i do something to make ANY sequence to take
>>the last value from his associated table, and not a "lost" value?
>
>This sounds like if the start of the sequence is set incorrectly:
>
>Try this : SELECT setval('NAME OF SEQUENCE', SOME_INTEGER, true);
>
>btw, you should also not expect a specific value from the sequence
>except that you will always get the next value from the sequence.
>it's also generally a bad idea to do select max(someid)+1 from
>table.
>The whole concept of a sequence is thus much better.

I think this is sound general advice for a production database.

However if you control the database such that you can prevent access to
it while you are updating it, you can run something like:

SELECT setval('NAME OF SEQUENCE', (select max(id)+1 from
table_of_sequence), true);

Where "table_of_sequence" is the name of the table which the sequence
is attached to.

The reason you don't use that syntax is that it's not multi-user safe.
But if you know there are no other users running changes to that
sequence when you run your updates, then you're good to go. It's a very
fast way to update all your tables to make sure the sequence #'s are
all valid, without having to look up the max value on each one (which
would also require that you shut off access to the table and for a much
longer time).

Hope that helps,

Steve