Hello all,
On a Postgres database when I explicitly insert a value into a column with a sequence on it the sequence doesn't get updated and can return ids that aren't unique. This can be be fixed with SQLAlchemy equivalent of: maxid = select max(id) on family; select setval(family_id_seq, maxid+1) ...or with a default=some_func_that_returns_maxid_plus_one on the column. The problem is this isn't fool proof since max(id) could change before the setval()/update in a multiuser/threaded environment. Its more or less the same problem if I don't use the sequence and have a serial column instead. On sqlite the id column seems to always generate a unique number. I'm not sure what other databases do. What's the best way to address this? Here's an example of whats happening: from sqlalchemy import * #uri = 'sqlite:///:memory:' # this test works fine on sqlite uri = 'postgres://server/test' engine = create_engine(uri, echo=False) metadata = MetaData(bind=engine) family_table = Table('family', metadata, Column('id', Integer, Sequence('family_id_seq'), primary_key=True), Column('data', String(32))) metadata.drop_all() metadata.create_all() family_table.insert(values={'id': 1}).execute(bind=engine) family_table.insert(values={'id': 2}).execute(bind=engine) # these two lines will fix the following error it but aren't guaranteed # to be safe #maxid = engine.execute('select max(id) from family').fetchone()[0] #engine.execute("select setval('family_id_seq', %s)" % (maxid + 1)) # raises IntegrityError since id won't be unique family_table.insert(values={'family': 'something'}).execute(bind=engine) --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---