On Tue, Jan 3, 2012 at 1:18 PM, Michael Bayer <mike...@zzzcomputing.com> wrote:
> Not sure what the issue is for the first aspect here, do you just need to > call Session.flush() ? > http://www.sqlalchemy.org/docs/orm/session.html#flushing That's exactly what I need to do :) > This is a common question, I was able to find a few mentions of it: > > http://stackoverflow.com/questions/8361513/sequence-without-a-gaps > http://stackoverflow.com/questions/4105147/alternative-to-maxid-in-complex-primary-key > > It's a PITA to create an unbroken chain of integers that is resilient against > transaction failures, because it essentially requires locking the entire > table. Usually, sequences are used to create increasing integer values, but > a key behavior of a sequence is that it never returns the same value again, > regardless of transactional state - this is specifically so that no matter > what happens, there is zero chance of a particular value ever conflicting > with one acquired from the same sequence elsewhere. > > So here you need to create the id based on the max(id) of what is already in > the table, and you also need to lock the table to ensure no other processes > do the same thing at the same time. The second answer above suggests that a > simple SELECT .. FOR UPDATE can be used here, the SQLAlchemy Query object > uses with_lockmode() to do this, which...apparently is not correctly > documented.... so you're basically looking to send the string "update" here: > > query(func.max(MyTable.id)).with_lockmode('update').scalar() > It's nice to see sqlalchemy provides a way to hold database locks. This is a good option but my customer didn't like this since he believes his system will be highly concurrent. I will just use another temporary table for fast inserting, then have a daemon process periodically read the temporary table and serialize writes on the final tables. Thanks for your help Michael. Thiago. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
