0

I developed an application using Postgresql + TimescaleDB + PostGIS where repeatedly some primary keys are out of sync. Fixing this manually as described in How to reset postgres' primary key sequence when it falls out of sync? works, but I want to understand why this happens and how I can avoid this.

  • The affected tables are not managed with TimescaleDB and have no PostGIS related columns.
  • My code contains only one insert statement per affected table and my code never sets primary-keys directly.
  • I'm using SqlAlchemy for the inserts, but only the SQLAlchemy Expression Language layer and not the ORM.

Does anybody have an idea how to fix this issue?

rocksportrocker
  • 7,251
  • 2
  • 31
  • 48
  • 1
    "*and my code never sets primary-keys directly*" - apparently it does, otherwise they wouldn't be out of sync. If you change all generated primary keys to `generated always as identity` you will get an error message when your code tries to provide an explicit value –  Apr 05 '22 at 16:59
  • 1
    Add the table definition to your question as an update. Also the `INSERT` statement. – Adrian Klaver Apr 05 '22 at 17:06
  • @a_horse_with_no_name so there is no other way how this can happen? Thanks for the advice using `generated always as identity`, did not know this yet. – rocksportrocker Apr 05 '22 at 17:34
  • 1
    Without seeing the table definition or the insert statement there is really no way to tell exactly what is going on. – Adrian Klaver Apr 05 '22 at 20:00

0 Answers0