update sequence on insert

[Brett]

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)

[Michael Bayer]

On Feb 15, 2008, at 5:53 PM, Brett wrote:

>
> 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:

why not rely upon the sequence unconditionally ? SA has arranged
things such that if you never create your own PK values, the
database's preferred methodology is used automatically (i.e. sqlite's
or mysql's autoincrement, PG's SERIAL/sequence).

[Brett]

I have to be able to dump my tables to CSV so I need to keep the primary
key values so my foreign keys match up.

[Michael Bayer]

after populating the tables with your CSV data, manually increment the
sequence to the next available id.