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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
