At 11:20 PM 11/24/2008, pgsql-sql-owner@postgresql.org wrote:
>Message-Id: <0FCB821D-C666-4FB9-B9FE-BA7B9EECE8C5@rvt.dds.nl>
>From: ries van Twisk <pg@rvt.dds.nl>
>To: Tk421 <vrobador@gmail.com>
>In-Reply-To: <492AFC8C.302@gmail.com>
>Subject: Re: Sequence and nextval problem
>Date: Mon, 24 Nov 2008 16:21:40 -0500
>References: <492AFC8C.302@gmail.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