On Jun 1, 2006, at 1:53 AM, Matt Goodall wrote:
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?The basic answer is don't try to prevent it and don't worry about it either.PostgreSQL sequence updates (nextval, setval, ?) are atomic, and outside of the current transaction, ensuring that multiple, concurrent calls to nextval() will each receive a unique value.See <http://www.postgresql.org/docs/8.1/interactive/functions- sequence.html> for details.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.Yeah, don't do that. You'll probably screw the sequence up.If you squish a hole in the sequence but PostgreSQL has already allocated the "hole" to another session then you're likely to start getting errors at INSERT time.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 can't happen. See above link.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.IIRC, MySQL does basically the same thing in reverse - you insert the row and then retrieve the allocated id. I assume it is as safe as the PostgreSQL sequence type.- Matt -- __ / \__ Matt Goodall, Pollenation Internet Ltd \__/ \ w: http://www.pollenation.net __/ \__/ e: [EMAIL PROTECTED] / \__/ \ t: +44 (0)113 2252500 \__/ \__/ / \ Any views expressed are my own and do not necessarily \__/ reflect the views of my employer. -------------------------------------------------------All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications inthe hosting industry. Fanatical Support. Click to learn morehttp://sel.as-us.falkag.net/sel? cmd=lnk&kid=107521&bid=248729&dat=121642_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
smime.p7s
Description: S/MIME cryptographic signature
