A duel with Postgres

For about 4 hours yesterday, I tried to figure out what went wrong when I was trying to insert some rows into a Postgres instance, and I was amazed when I found out why? You might be too!

I was working with my GSoC project, with the MSS repository. I was working on separting test, and deployment data setup. reference. Parallelly, I was adding support for a Postgres database to mscolab. I’d never used Postgres before. I was having some real fun setting up demodata for tests, until the tests failed logging the following:

context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f2d389fb198> E psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "projects_pkey" E DETAIL: Key (id)=(1) already exists.

My first thought was the autoincrement id-counter doesn’t start at all. So I Googled and ‘Stackoverflow’ed errors where the autoincrement counter doesn’t work. https://stackoverflow.com/questions/20848300/unable-to-create-autoincrementing-primary-key-with-flask-sqlalchemy However, later on a test run, I realized that it worked just fine for the deployment setup, where I didn’t explicitly put any rows into DB. I was sort of confused as to both insertions(demodata, and actual data) were being done programmatically. So what could possibly change in both the setups? After recursive hopping from thread to thread and I found this, TL;DR What?! Sequential counter doesn’t change upon explicit insertions? Ugh. https://www.postgresql.org/message-id/20041130052832.GA87521%40winnie.fuhr.org That’s VERY WEIRD design, isn’t it? I’ve never come across this kind of obstacles while dealing with MySQL, Mongo, or Sqlite. I continued my search for a solution, an obvious thought was to reset the ‘id-counter’ each time I insert explicitly, or after a batch insert.

1. https://stackoverflow.com/questions/9108833/postgres-autoincrement-not-updated-on-explicit-id-inserts
2. https://stackoverflow.com/questions/5342440/reset-auto-increment-counter-in-postgres

I am still unsure if it’s a problem with Postgresql or a bunch of local setups. If you know what went wrong, please let me know via email or comments below. And I got it working by ‘ALTER’ing the projects_id_key to an appropriate value after demodata setup. https://bitbucket.org/plant99/mss/src/cea4408d7c9c0ac68649d5b20ad0cc86571d828b /mslib/mscolab/demodata.py#lines-214

Also, I hope you have a nice day. Thanks for reading my blog!