At 11:20 PM 11/24/2008, [EMAIL PROTECTED] wrote:
Message-Id: <[EMAIL PROTECTED]>
From: ries van Twisk <[EMAIL PROTECTED]>
To: Tk421 <[EMAIL PROTECTED]>
In-Reply-To: <[EMAIL PROTECTED]>
Subject: Re: Sequence and nextval problem
Date: Mon, 24 Nov 2008 16:21:40 -0500
References: <[EMAIL PROTECTED]>
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
