2

I am facing a weird problem using alembic migration. I am using Postgres as DB.

I have written an alembic revision where I am inserting some data in a table(There is already some data in the table before migration).

The issue is the migration runs successfully and inserted the data in the table. But after this when I am calling an api and trying to insert a new row I get some error, because table seq_id is not incremented.

ERROR:__name__:Failed to create a duplicate entry: (psycopg2.IntegrityError) duplicate key value violates unique constraint "user_pkey"
DETAIL:  Key (id)=(9) already exists.
 [SQL: 'INSERT INTO user (id, name, age, number) VALUES (%(id)s, %(name)s, %(age)s, %(number)s) RETURNING id'] [parameters: {'number': None, 'id': 9, 'name': 'something', 'age': 12}]

Let's take an example:

I have table "User" which already have 10 rows, now using alembic migration I am inserting two rows so now there are 12 rows. Ideally, it should update the sequence_id too, but seq_id is still at 10.

No using an api I am inserting on more row which it will try to add at id 11 and will give the above error. If I try this api two more times then it will be a success because it will increment the seq_id every time.

This is happening only after applying alembic migration, else it is working fine.

Can anyone tell me what I am doing wrong, did I miss any config, why the sequence id is not incrementing while applying migration?

Thanks in advance!

A J
  • 3,684
  • 2
  • 19
  • 24
  • are you sure it should "increment" sequence?.. – Vao Tsun Feb 10 '18 at 14:33
  • If it should not, then what is wrong here, some issue with alembic? Because inserting through API works fine, but after migration this problem occurs. Thanks. – A J Feb 11 '18 at 04:24
  • I would recommend altering your answer to include the schema of the table you are having issues with (you can use pg_dump for this), as well as the migration that is adding records that does not appear to increment the sequence. What you're describing should work correctly, but there might be something specific about your setup. – Mark Hildreth Feb 11 '18 at 20:19

1 Answers1

3

After all the research and read, what I found is in postgres it maintains a separate table to save sequence_id, not like in mysql it handles in the table.

So when you are applying migration or fixture in python you need to increment or reset the seq_id value after all the insert.

Something like this - 

op.execute(‘’'
       SELECT setval(‘“user_id_seq”‘, (SELECT max(“id”) from user));
   ‘’')
A J
  • 3,684
  • 2
  • 19
  • 24