the "nextval" function on sequences always give a unique value
regardless of transaction status. you are guaranteed to be the only
actor ever to have that ID; there is no chance of that ID being used
by a different client (unless you reset the sequence).
the PG docs, over at http://www.postgresql.org/docs/8.1/interactive/
functions-sequence.html , have this to say:
nextval
Advance the sequence object to its next value and return that
value. This is done atomically: even if multiple sessions execute
nextval concurrently, each will safely receive a distinct sequence
value.
as well as:
Important: To avoid blocking of concurrent transactions that obtain
numbers from the same sequence, a nextval operation is never rolled
back; that is, once a value has been fetched it is considered used,
even if the transaction that did the nextval later aborts. This means
that aborted transactions may leave unused "holes" in the sequence of
assigned values. setval operations are never rolled back, either.
if youre using the sequence for a primary key, its best to leave it
that way; playing games with max(id) is not going to produce atomic
results. you generally shouldnt care about the "sequentialness" of
primary keys since that's assigning meaning to them. if you need a
sequence of numbers with no gaps, thats a meaningful concept and
should be in some other column....you can use a sequence for that as
well but youd have to use "setval" to stick it back where you want if
it gets incremented unintentionally.
On May 31, 2006, at 7:36 PM, Koen Bok wrote:
Hello, I have two questions concerning sequences.
Whenever an INSERT fails and a ROLLBACK occurs, the sequence is
skipped by one. What is the best way to prevent this? I could do a
SELECT max(id) FROM table and reset the sequence with it by making
a trigger on the table. But I don't think this will work while
sqlachemy does a SELECT nextval() before it inserts.
There starts my second question. As I understand it, sqlalchemy
submits a new id by first selecting one from the sequence. But what
if between these two queries the same id was given to another
client? That would result in an error and therefore a ROLLBACK
right? Is there a way to do this on a database level?
PS. I use Postgres, I don't know how this works for other databases.
-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
